在电子表格中垂直复制所有列 [英] Copy all the columns vertically in a spreadsheet
问题描述
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的Sheet A的Sheet A,然后将Sheet1的Sheet B复制到Sheet2的A列。
I am trying to copy all the columns in one sheet to another sheet one below the another. Basically copying Column A of Sheet1 to Column A of Sheet2 and then Column B of Sheet1 below Column A of Sheet2 .
数据从每一列的第A页第4行开始,所以我保留第二个 For循环
从4运行非常感谢任何帮助,因为我几个小时了。当我运行代码时,它进入无限循环。
Data starts from row 4th of Sheet A in every column, so I kept the second For Loop
running from 4. Any help is greatly appreciated as I am stuck for hours now. When I run the code it goes in to infinite loop.
推荐答案
如果 Lastrow
对于每一列是相同的(引用 B
列)然后你可以尝试下面:
If the Lastrow
for every column is the same (referenced to B
Column) then you can try below:
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
即可复制下一列一(1)个循环。
You don't need to copy each cell one by one by doing nested For Loop
.
You can set the first group of cell to copy (Range("A4:A" & LastRow)
), then just use Offset
to copy the next column using just one(1) loop.
如果 LastR ow
对于每一列是不同的,你可以尝试:
If LastRow
for every column is different, you can try:
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
这篇关于在电子表格中垂直复制所有列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!