von NewbieJS » Mi, 28.06.2017 21:34
Hallo,
ich habe eine Datei bekommen und wollte diese erweitern bzw. bearbeiten - diese hat Makros womit ich mich gar nicht auskenne. Folgendes Formular wird ausgefüllt mit Kunden und Zeitnachweis und dann in eine Gesamtübersicht übertragen; es kommt vor dass sich die Kunden wiederholen aber die Zeit wird nicht zusammen gerechnet. Kann mir jemand anhand vom Code weiter helfen? Ich habe keine Ahnung davon und bin total verzweifelt:
Code: Alles auswählen
Sub DatenŸbertrag_Luka()
'
' DatenŸbertrag_Luka Makro
'
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Range("C5").Select
Sheets("Luka Zeitnachweis").Select
Range("B12:E12").Select
ActiveCell.FormulaR1C1 = "1/1/2017"
Sheets("Luka StundenŸbersicht").Select
Range("B5").Select
ActiveCell.FormulaR1C1 = "1/1/2017"
Sheets("Luka Zeitnachweis").Select
Range("F12:I12").Select
ActiveCell.FormulaR1C1 = "1/2/2017"
Sheets("Luka StundenŸbersicht").Select
Range("C5").Select
ActiveCell.FormulaR1C1 = "1/2/2017"
Sheets("Luka Zeitnachweis").Select
Range("J12:M12").Select
ActiveCell.FormulaR1C1 = "1/3/2017"
Sheets("Luka StundenŸbersicht").Select
Range("D5").Select
ActiveCell.FormulaR1C1 = "1/3/2017"
Sheets("Luka Zeitnachweis").Select
Range("N12:Q12").Select
ActiveCell.FormulaR1C1 = "1/4/2017"
Sheets("Luka StundenŸbersicht").Select
Range("E5").Select
ActiveCell.FormulaR1C1 = "1/4/2017"
Sheets("Luka Zeitnachweis").Select
Range("R12:U12").Select
ActiveCell.FormulaR1C1 = "1/5/2017"
Sheets("Luka StundenŸbersicht").Select
Range("F5").Select
ActiveCell.FormulaR1C1 = "1/5/2017"
Sheets("Luka Zeitnachweis").Select
Range("V12:Y12").Select
ActiveCell.FormulaR1C1 = "1/6/2017"
Sheets("Luka StundenŸbersicht").Select
Range("G5").Select
ActiveCell.FormulaR1C1 = "1/6/2017"
Rows("16:16").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Sheets("Luka Zeitnachweis").Select
Range("A14:A29").Select
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("E14:E29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("I14:I29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("M14:M29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("D6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("Q14:Q29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("U14:U29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("F6").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("Y14:Y29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("G6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("5:21").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort.SortFields.Add Key:= _
Range("A5:S5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort
.SetRange Range("A5:S21")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Sheets("Luka Zeitnachweis").Select
Range("B12:Y12").Select
Selection.ClearContents
Range("B14:D29").Select
Selection.ClearContents
Range("F14:H29").Select
Selection.ClearContents
Range("J14:L29").Select
Selection.ClearContents
Range("N14:P29").Select
Selection.ClearContents
Range("R14:T29").Select
Selection.ClearContents
Range("V14:X29").Select
Selection.ClearContents
End Sub
Sub DatenŸbertragungLuka()
'
' DatenŸbertragungLuka Makro
'
'
Range("A14:A29").Select
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("B12:E12").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1/1/2017"
Sheets("Luka StundenŸbersicht").Select
Range("B4").Select
ActiveCell.FormulaR1C1 = "1/1/2017"
Sheets("Luka Zeitnachweis").Select
Range("F12:I12").Select
ActiveCell.FormulaR1C1 = "1/2/2017"
Sheets("Luka StundenŸbersicht").Select
Range("C4").Select
ActiveSheet.Paste
Sheets("Luka Zeitnachweis").Select
Range("J12:M12").Select
ActiveCell.FormulaR1C1 = "1/3/2017"
Sheets("Luka StundenŸbersicht").Select
Range("D4").Select
ActiveCell.FormulaR1C1 = "1/3/2017"
Sheets("Luka Zeitnachweis").Select
Range("N12:Q12").Select
ActiveCell.FormulaR1C1 = "1/4/2017"
Sheets("Luka StundenŸbersicht").Select
Range("E4").Select
ActiveCell.FormulaR1C1 = "1/4/2017"
Sheets("Luka Zeitnachweis").Select
Range("R12:U12").Select
ActiveCell.FormulaR1C1 = "1/5/2017"
Sheets("Luka StundenŸbersicht").Select
Range("F4").Select
ActiveCell.FormulaR1C1 = "1/5/2017"
Sheets("Luka Zeitnachweis").Select
Range("V12:Y12").Select
ActiveCell.FormulaR1C1 = "1/6/2017"
Sheets("Luka StundenŸbersicht").Select
Range("G4").Select
ActiveCell.FormulaR1C1 = "1/6/2017"
Range("H12").Select
Sheets("Luka Zeitnachweis").Select
Range("E14:E29").Select
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("I14:I29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("C5").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("M14:M29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("D5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("Q14:Q29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("E5").Select
Sheets("Luka Zeitnachweis").Select
Range("Q14:Q29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("U14:U29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("F5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("Y14:Y29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("G5").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("4:10").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort.SortFields.Add Key:= _
Range("A4:G4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort
.SetRange Range("A4:G10")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Sheets("Luka Zeitnachweis").Select
Range("B12:E12").Select
Selection.ClearContents
Range("F12:I12").Select
Selection.ClearContents
Range("J12:M12").Select
Selection.ClearContents
Range("N12:Q12").Select
Selection.ClearContents
Range("R12:U12").Select
Selection.ClearContents
Range("V12:Y12").Select
Selection.ClearContents
Range("B14:D29").Select
Selection.ClearContents
Range("F14:H29").Select
Selection.ClearContents
Range("J14:L29").Select
Selection.ClearContents
Range("N14:P29").Select
Selection.ClearContents
Range("R14:T19").Select
Selection.ClearContents
Range("V14:X29").Select
Selection.ClearContents
Sheets("Luka StundenŸbersicht").Select
Range("B5").Select
End Sub
_________________
- Moderation:
Zur übersichtlichen Darstellung des Textes und vom Programmcode im Text [code][/code] Tags gesetzt.
lorbass, Moderator
[attachment=0]Stundennachweis2.xlsm[/attachment][attachment=0]Stundennachweis2.xlsm[/attachment][attachment=0]Stundennachweis2.xlsm[/attachment]Hallo,
ich habe eine Datei bekommen und wollte diese erweitern bzw. bearbeiten - diese hat Makros womit ich mich gar nicht auskenne. Folgendes Formular wird ausgefüllt mit Kunden und Zeitnachweis und dann in eine Gesamtübersicht übertragen; es kommt vor dass sich die Kunden wiederholen aber die Zeit wird nicht zusammen gerechnet. Kann mir jemand anhand vom Code weiter helfen? Ich habe keine Ahnung davon und bin total verzweifelt:
[code]Sub DatenŸbertrag_Luka()
'
' DatenŸbertrag_Luka Makro
'
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Range("C5").Select
Sheets("Luka Zeitnachweis").Select
Range("B12:E12").Select
ActiveCell.FormulaR1C1 = "1/1/2017"
Sheets("Luka StundenŸbersicht").Select
Range("B5").Select
ActiveCell.FormulaR1C1 = "1/1/2017"
Sheets("Luka Zeitnachweis").Select
Range("F12:I12").Select
ActiveCell.FormulaR1C1 = "1/2/2017"
Sheets("Luka StundenŸbersicht").Select
Range("C5").Select
ActiveCell.FormulaR1C1 = "1/2/2017"
Sheets("Luka Zeitnachweis").Select
Range("J12:M12").Select
ActiveCell.FormulaR1C1 = "1/3/2017"
Sheets("Luka StundenŸbersicht").Select
Range("D5").Select
ActiveCell.FormulaR1C1 = "1/3/2017"
Sheets("Luka Zeitnachweis").Select
Range("N12:Q12").Select
ActiveCell.FormulaR1C1 = "1/4/2017"
Sheets("Luka StundenŸbersicht").Select
Range("E5").Select
ActiveCell.FormulaR1C1 = "1/4/2017"
Sheets("Luka Zeitnachweis").Select
Range("R12:U12").Select
ActiveCell.FormulaR1C1 = "1/5/2017"
Sheets("Luka StundenŸbersicht").Select
Range("F5").Select
ActiveCell.FormulaR1C1 = "1/5/2017"
Sheets("Luka Zeitnachweis").Select
Range("V12:Y12").Select
ActiveCell.FormulaR1C1 = "1/6/2017"
Sheets("Luka StundenŸbersicht").Select
Range("G5").Select
ActiveCell.FormulaR1C1 = "1/6/2017"
Rows("16:16").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Sheets("Luka Zeitnachweis").Select
Range("A14:A29").Select
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("E14:E29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("I14:I29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("M14:M29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("D6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("Q14:Q29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("U14:U29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("F6").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("Y14:Y29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("G6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("5:21").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort.SortFields.Add Key:= _
Range("A5:S5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort
.SetRange Range("A5:S21")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Sheets("Luka Zeitnachweis").Select
Range("B12:Y12").Select
Selection.ClearContents
Range("B14:D29").Select
Selection.ClearContents
Range("F14:H29").Select
Selection.ClearContents
Range("J14:L29").Select
Selection.ClearContents
Range("N14:P29").Select
Selection.ClearContents
Range("R14:T29").Select
Selection.ClearContents
Range("V14:X29").Select
Selection.ClearContents
End Sub
Sub DatenŸbertragungLuka()
'
' DatenŸbertragungLuka Makro
'
'
Range("A14:A29").Select
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("B12:E12").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1/1/2017"
Sheets("Luka StundenŸbersicht").Select
Range("B4").Select
ActiveCell.FormulaR1C1 = "1/1/2017"
Sheets("Luka Zeitnachweis").Select
Range("F12:I12").Select
ActiveCell.FormulaR1C1 = "1/2/2017"
Sheets("Luka StundenŸbersicht").Select
Range("C4").Select
ActiveSheet.Paste
Sheets("Luka Zeitnachweis").Select
Range("J12:M12").Select
ActiveCell.FormulaR1C1 = "1/3/2017"
Sheets("Luka StundenŸbersicht").Select
Range("D4").Select
ActiveCell.FormulaR1C1 = "1/3/2017"
Sheets("Luka Zeitnachweis").Select
Range("N12:Q12").Select
ActiveCell.FormulaR1C1 = "1/4/2017"
Sheets("Luka StundenŸbersicht").Select
Range("E4").Select
ActiveCell.FormulaR1C1 = "1/4/2017"
Sheets("Luka Zeitnachweis").Select
Range("R12:U12").Select
ActiveCell.FormulaR1C1 = "1/5/2017"
Sheets("Luka StundenŸbersicht").Select
Range("F4").Select
ActiveCell.FormulaR1C1 = "1/5/2017"
Sheets("Luka Zeitnachweis").Select
Range("V12:Y12").Select
ActiveCell.FormulaR1C1 = "1/6/2017"
Sheets("Luka StundenŸbersicht").Select
Range("G4").Select
ActiveCell.FormulaR1C1 = "1/6/2017"
Range("H12").Select
Sheets("Luka Zeitnachweis").Select
Range("E14:E29").Select
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("I14:I29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("C5").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("M14:M29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("D5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("Q14:Q29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("E5").Select
Sheets("Luka Zeitnachweis").Select
Range("Q14:Q29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("U14:U29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("F5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Luka Zeitnachweis").Select
Range("Y14:Y29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Luka StundenŸbersicht").Select
Range("G5").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("4:10").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort.SortFields.Add Key:= _
Range("A4:G4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Luka StundenŸbersicht").Sort
.SetRange Range("A4:G10")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Sheets("Luka Zeitnachweis").Select
Range("B12:E12").Select
Selection.ClearContents
Range("F12:I12").Select
Selection.ClearContents
Range("J12:M12").Select
Selection.ClearContents
Range("N12:Q12").Select
Selection.ClearContents
Range("R12:U12").Select
Selection.ClearContents
Range("V12:Y12").Select
Selection.ClearContents
Range("B14:D29").Select
Selection.ClearContents
Range("F14:H29").Select
Selection.ClearContents
Range("J14:L29").Select
Selection.ClearContents
Range("N14:P29").Select
Selection.ClearContents
Range("R14:T19").Select
Selection.ClearContents
Range("V14:X29").Select
Selection.ClearContents
Sheets("Luka StundenŸbersicht").Select
Range("B5").Select
End Sub[/code]
_________________
[list][b]Moderation:[/b]
Zur übersichtlichen Darstellung des Textes und vom Programmcode im Text [code][/code] Tags gesetzt.
lorbass, Moderator[/list]