set ws1 As SheetA
set ws2 As Target
With ws1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
Lastrow = ws1.Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To Lastcol
For l = 4 To lastrow
ws1.Cells(l, i).Copy ws2.Range("a65536").End(xlUp).Offset(1, 0)
Next l
Next i
我正在尝试将一张纸上的所有列复制到另一张纸下的另一张纸上。基本上将Sheet1的A列复制到Sheet2的A列,然后将Sheet1的B列复制到Sheet2的A列以下。
数据从每一列的工作表A的第4行开始,因此我保持第二行从第4行开始。For Loop
由于我被困了几个小时,因此非常感谢您的帮助。当我运行代码时,它会进入无限循环。
如果Lastrow
每个列的相同(引用到B
Column),则可以尝试以下操作:
With ws1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
Dim i As Long, LastRow2 As Long
For i = 0 To LastCol - 1
With ws2
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
ws1.Range("A4:A" & LastRow).Offset(0, i).Copy .Range("A" & LastRow2)
End With
Next
您无需通过嵌套操作逐个复制每个单元格For Loop
。
您可以将第一组单元格设置为复制(Range("A4:A" & LastRow)
),然后仅使用Offset
one(1)循环即可复制下一列。
如果LastRow
每个列都不相同,则可以尝试:
Dim LastCol As Long, LastRow As Long
LastCol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column
Dim i As Long, LastRow2 As Long
For i = 0 To LastCol - 1
LastRow = ws1.Cells(ws1.Rows.Count, i + 1).End(xlUp).Row
With ws2
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
ws1.Range("A4:A" & LastRow).Offset(0, i).Copy .Range("A" & LastRow2)
End With
Next
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句