|
Hallo,
ich versuche gerade Werte aus einer Tabelle in eine andere Tabelle zu übertragen. Dabei gehe ich Zeilenweise mit einer For Next Schleife vor, nur einen Bereich müsste ich kopieren und dann transponiert einfügen. Leider geht das nicht so einfach (= fett markierter Teil). Die Variable lngRow kann ich nicht verwenden, aber wie kann ich es sonst lösen? Für Tipps wäre ich sehr dankbar. Auch für Tipps um den Code ein wenig schöner zu machen wären hilfreich.
Danke!
Saied
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 = lngn + 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 = lngn + 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 = lngn + 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 = lngn + 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 = lngn + 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 = lngn + 1
End If
Next lngRow
End With
End Sub
|