AW: spalten
11.08.2022 16:59:03
Yal
Hallo Klaus,
probiere folgendes:
Sub Spalten_kopieren()
Dim wsQ As Worksheet 'Q wie Quelle
Dim wsZ As Worksheet 'Z wie Ziel
Dim C 'C wie Column
Dim ZielZeile As Long
Dim i As Long
Set wsZ = ThisWorkbook.Worksheets("Ergebnis")
For Each wsQ In ThisWorkbook.Worksheets
i = 0
For Each C In Array("C", "F", "G", "J")
i = i + 1
If wsQ.Name wsZ.Name Then
If i = 1 Then ZielZeile = NeueZeile(wsZ.Range("A:D"))
wsQ.Range(wsQ.Cells(1, C), wsQ.Cells(Rows.Count, C).End(xlUp)).Copy
wsZ.Cells(ZielZeile, i).PasteSpecial xlPasteValues
End If
Next
Next
End Sub
Private Function NeueZeile(R As Range)
Dim C As Range
Dim Erg As Long
For Each C In R.Columns
Erg = WorksheetFunction.Max(Erg, R.Parent.Cells(Rows.Count, C.Column).End(xlUp).Row)
Next
NeueZeile = Erg
End Function
VG
Yal