你可以改变
Range("B3:B65536").Copy Destination:=Sheets("DB").Range("B" & lastrow)
to
Range("B3:B65536").Copy
Sheets("DB").Range("B" & lastrow).PasteSpecial xlPasteValues
顺便说一句,如果您有 xls 文件(excel 2003),如果您的lastrow
会更大 3.
尝试改用此代码:
Sub Get_Data()
Dim lastrowDB As Long, lastrow As Long
Dim arr1, arr2, i As Integer
With Sheets("DB")
lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
arr1 = Array("B", "C", "D", "E", "F", "AH", "AI", "AJ", "J", "P", "AF")
arr2 = Array("B", "A", "C", "P", "D", "E", "G", "F", "H", "I", "J")
For i = LBound(arr1) To UBound(arr1)
With Sheets("Sheet1")
lastrow = Application.Max(3, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
.Range(.Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Copy
Sheets("DB").Range(arr2(i) & lastrowDB).PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
End Sub
注意,上面的代码确定了最后一个非空行DB
列中的工作表A
(多变的lastrowDB
)。如果您需要查找每个目标列的最后一行DB
工作表,使用下一个修改:
For i = LBound(arr1) To UBound(arr1)
With Sheets("DB")
lastrowDB = .Cells(.Rows.Count, arr2(i)).End(xlUp).Row + 1
End With
' NEXT CODE
Next
您也可以使用下一个方法代替Copy/PasteSpecial
。代替
.Range(.Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Copy
Sheets("DB").Range(arr2(i) & lastrowDB).PasteSpecial xlPasteValues
with
Sheets("DB").Range(arr2(i) & lastrowDB).Resize(lastrow - 2).Value = _
.Range(.Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Value