Ich habe ein simples Makro aufgenommen und es funktioniert eigentlich alles, mein Problem ist lediglich dass ich dieses Makro an verschiedene Listen anwenden werde.
Sprich die "Range:kann auch mal weiter als G64979 gehen, zum Beispiel bis G120000.
Wie muss ich deshalb dieses Makro anpassen, dass Excel so weit ausfüllt bis zum letzten Eintrag?:
Sub TelebalanceTest5()
' TelebalanceTest5 Makro
Rows("1:14").Select
Range("H1").Activate
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$G$64979").AutoFilter Field:=3, Criteria1:=Array( _
"1", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "2", "20", "21", "22", "23", _
"24", "25", "26", "27", "28", "29", "3", "30", "31", "32", "33", "34", "35", "36", "37", "38", _
"39", "4", "40", "41", "42", "5", "6", "7", "8", "9"), Operator:=xlFilterValues
Cells.Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
ActiveCell.FormulaR1C1 = "Uhrzeit"
Rows("1:1").Select
Selection.AutoFilter
Columns("B:B").Select
Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
Range("A1").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("A:A").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("C:C").ColumnWidth = 18.57
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("E1").Select
ActiveCell.FormulaR1C1 = "Desktop"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Phone"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Tablet"
Range("F2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]/100*RC[2]"
Range("G2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-3]/100*RC[2]"
Range("E2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[1]-RC[2]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E63539")
Range("E2:E63539").Select
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F63539")
Range("F2:F63539").Select
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G63539")
Range("G2:G63539").Select
Columns("G:G").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("F:F").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("E:E").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("H:L").Select
Selection.Delete Shift:=xlToLeft
Sheets("Tabelle1").Select
Sheets("Tabelle1").Name = "07.01.23"
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Range("B10").Select
Sheets("1 Telebalance FTP").Select
ActiveWindow.SelectedSheets.Delete
Range("I12").Select
ActiveWorkbook.Save
Columns("E:G").Select
End Sub