|
Danke für die schnelle Antwort! Es hängt noch an dem fett markierten. Da bräuchte ich noch eine Lösung, damit der Code auch transponiert einfügt:
Sub CopyPrim()
Dim lngRow As Long
Dim lngRowmax As Long
Dim lngn As Long
Dim lngz As Long
lngn = 2
With Worksheets("Mapping_#1")
lngRowmax = .UsedRange.Rows.Count
For lngRow = 4 To lngRowmax
If .Cells(lngRow, 17).Value <> "" Then
.Cells(lngRow, 17).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 6)
.Cells(lngRow, 18).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 7)
.Cells(lngRow, 3).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 8)
.Cells(lngRow, 4).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 9)
.Cells(lngRow, 6).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 10)
.Cells(lngRow, 8).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 12)
.Range(Cells(lngRow, 21), Cells(lngRow, 22)).copy
Worksheets("GL_Cust_1001_Makro").Range("Q2").PasteSpecial _
Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
lngn = Worksheets("GL_Cust_1001_Makro").UsedRange.Rows.Count + 1
.Cells(lngRow, 17).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 6)
.Cells(lngRow, 18).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 7)
.Cells(lngRow, 3).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 8)
.Cells(lngRow, 4).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 9)
.Cells(lngRow, 6).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 10)
.Cells(lngRow, 8).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 12)
lngn = Worksheets("GL_Cust_1001_Makro").UsedRange.Rows.Count + 1
.Cells(lngRow, 17).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 6)
.Cells(lngRow, 18).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 7)
.Cells(lngRow, 3).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 8)
.Cells(lngRow, 4).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 9)
.Cells(lngRow, 6).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 10)
.Cells(lngRow, 8).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 12)
lngn = Worksheets("GL_Cust_1001_Makro").UsedRange.Rows.Count + 1
.Cells(lngRow, 17).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 6)
.Cells(lngRow, 18).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 7)
.Cells(lngRow, 3).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 8)
.Cells(lngRow, 4).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 9)
.Cells(lngRow, 6).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 10)
.Cells(lngRow, 8).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 12)
lngn = Worksheets("GL_Cust_1001_Makro").UsedRange.Rows.Count + 1
.Cells(lngRow, 17).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 6)
.Cells(lngRow, 18).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 7)
.Cells(lngRow, 3).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 8)
.Cells(lngRow, 4).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 9)
.Cells(lngRow, 6).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 10)
.Cells(lngRow, 8).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 12)
lngn = Worksheets("GL_Cust_1001_Makro").UsedRange.Rows.Count + 1
.Cells(lngRow, 17).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 6)
.Cells(lngRow, 18).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 7)
.Cells(lngRow, 3).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 8)
.Cells(lngRow, 4).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 9)
.Cells(lngRow, 6).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 10)
.Cells(lngRow, 8).copy Destination:=Worksheets("GL_Cust_1001_Makro").Cells(lngn, 12)
lngn = Worksheets("GL_Cust_1001_Makro").UsedRange.Rows.Count + 1
End If
Next lngRow
End With
End Sub
|