Das folgende Makro wurde aufgezeichnet, weshalb es wohl unnötig lang wird. Nur hat es nun einen Laufzeitfehler, welchen ich nicht finde.
Folgendes sollte das Makro machen: Sheet "Soll-Liste SAP" soll kopiert werden. Im neuen Sheet werden alle Links entfernt (Alles markieren, kopieren und nur Werte einfügen). Danach werden die Zeilen nach Spalte A sortiert. Ist dies geschehen, können die Werte nochmals kopiert werden (aber ohne Spalte A, die ist nur zum sortieren) dann eine Vorlage öffnen und die Werte da einfügen. Speichern unter..., das "neue Sheet" kann wieder gelöscht werden.
Gruss André
Private Sub CommandButton1_Click()
Sheets("SOLL-Liste SAP").Select
Sheets("SOLL-Liste SAP").Copy After:=Sheets(4)
Sheets("SOLL-Liste SAP (2)").Select
ActiveWindow.Panes(1).Activate
Range("A1:AA1000").Select
Range("A1:AA1000").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.Panes(3).Activate
Range("A10:AS10").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-408
ActiveWindow.ScrollRow = 366
ActiveWindow.ScrollRow = 361
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 349
ActiveWindow.ScrollRow = 342
ActiveWindow.ScrollRow = 335
ActiveWindow.ScrollRow = 327
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 312
ActiveWindow.ScrollRow = 308
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 294
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 207
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 157
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("SOLL-Liste SAP (2)").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SOLL-Liste SAP (2)").Sort.SortFields.Add2 Key:= _
Range("A10"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("SOLL-Liste SAP (2)").Sort
.SetRange Range("A10:AS810")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("E14").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ChDir _
"I:\D5\Engineering & Services\Engineering\20 Energie\03 Einkauf und Verrechnung\01 _
Elektrizität\B Stromverrechnung\Einheitstarif\Produktiv"
Workbooks.Open Filename:= _
"I:\D5\Engineering & Services\Engineering\20 Energie\03 Einkauf und Verrechnung\01 _
Elektrizität\B Stromverrechnung\Einheitstarif\Produktiv\VORLAGE 04a - SAP-Upload Strom ET KST. _
xlsx"
Windows("MAKRO 03a - ET Stromkosten Kostenstellen.xlsm").Activate
Range("B10:AS10").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-81
Selection.Copy
Windows("VORLAGE 04a - SAP-Upload Strom ET KST.xlsx").Activate
Range("B11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=504
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 523
ActiveWindow.ScrollRow = 537
ActiveWindow.ScrollRow = 552
ActiveWindow.ScrollRow = 566
ActiveWindow.ScrollRow = 580
ActiveWindow.ScrollRow = 594
ActiveWindow.ScrollRow = 623
ActiveWindow.ScrollRow = 637
ActiveWindow.ScrollRow = 651
ActiveWindow.ScrollRow = 666
ActiveWindow.ScrollRow = 680
ActiveWindow.ScrollRow = 694
ActiveWindow.ScrollRow = 708
ActiveWindow.ScrollRow = 694
ActiveWindow.ScrollRow = 680
ActiveWindow.ScrollRow = 666
ActiveWindow.ScrollRow = 651
ActiveWindow.ScrollRow = 637
ActiveWindow.ScrollRow = 623
ActiveWindow.ScrollRow = 609
ActiveWindow.ScrollRow = 594
ActiveWindow.ScrollRow = 580
ActiveWindow.ScrollRow = 566
ActiveWindow.ScrollRow = 580
ActiveWindow.ScrollRow = 594
ActiveWindow.ScrollRow = 609
ActiveWindow.ScrollRow = 594
ActiveWindow.ScrollRow = 580
ActiveWindow.ScrollRow = 581
Rows("587:590").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=6
Rows("591:607").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("E602").Select
ActiveWindow.SmallScroll Down:=-15
ActiveWindow.ScrollRow = 566
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 466
ActiveWindow.ScrollRow = 395
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 11
ActiveWorkbook.SaveAs Filename:= _
"I:\D5\Engineering & Services\Engineering\20 Energie\03 Einkauf und Verrechnung\01 _
Elektrizität\B Stromverrechnung\Einheitstarif\Produktiv\" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Windows("MAKRO 03a - ET Stromkosten Kostenstellen.xlsm").Activate
Sheets("SOLL-Liste SAP (2)").Select
ActiveWindow.SelectedSheets.Delete
End Sub