AW: Namen aller geöffneten .XLS in VBA Array speichern
29.11.2013 14:37:40
Alexander
Hallo Rudi,
vielen Dank für die Antwort. Ich plane dies noch für weiter Zellen zu tun. Ich habe den Code daher wie folgt ergänzt.:
Sub Makro1()
Dim aktiveXLS As Workbook
Dim wkbBasis As Workbook
Set wkbBasis = Workbooks("Basis Auswertung.xlsm")
For Each aktiveXLS In Workbooks
If Not aktiveXLS Is wkbBasis Then
aktiveXLS.Sheets(1).Range("A3:F3").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("G3:J3").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("G5:J5").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 3).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B6").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 4).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B7").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 5).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("A10:B10").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 6).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("C10:J10").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 7).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I21").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 8).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I27").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 9).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I34").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 10).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I40").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 11).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I46").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 12).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B23:J23").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 13).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B29:J29").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 14).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B36:J36").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 15).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B42:J42").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 16).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B48:J48").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 17).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I24").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 18).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I30").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 19).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I37").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 20).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I43").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 21).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I49").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 22).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B26:J26").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 23).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B32:J32").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 24).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B39:J39").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 25).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B45:J45").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 26).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B51:J51").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 27).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I58").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 28).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I64").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 29).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I70").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 30).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B60:J60").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 31).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B66:J66").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 32).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B72:J72").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 33).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I61").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 34).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I67").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 35).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("I73").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 36).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B63:J63").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 37).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B69:J69").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 38).End(xlUp).Offset(1).PasteSpecial xlPasteValues
aktiveXLS.Sheets(1).Range("B75:J75").Copy
wkbBasis.Sheets(1).Cells(Rows.Count, 39).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Next
End Sub
Leider werden die Daten fehlerhaft kopiert. Hab ich die Zeilen richtig abgeändert?
Gruß
Alex