Copy Paste Values only( xlPasteValues )
If you are wanting to just copy the whole column, you can simplify the code a lot by doing something like this:
Sub CopyCol()
Sheets("Sheet1").Columns(1).Copy
Sheets("Sheet2").Columns(2).PasteSpecial xlPasteValues
End Sub
Or
Sub CopyCol()
Sheets("Sheet1").Columns("A").Copy
Sheets("Sheet2").Columns("B").PasteSpecial xlPasteValues
End Sub
Or if you want to keep the loop
Public Sub CopyrangeA()
Dim firstrowDB As Long, lastrow As Long
Dim arr1, arr2, i As Integer
firstrowDB = 1
arr1 = Array("BJ", "BK")
arr2 = Array("A", "B")
For i = LBound(arr1) To UBound(arr1)
Sheets("Sheet1").Columns(arr1(i)).Copy
Sheets("Sheet2").Columns(arr2(i)).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
End Sub
since you only want values copied, you can pass the values of arr1
directly to arr2
and avoid copy/paste.
code inside the For
loop, inside the With
block, after lastrow
calculation:
Sheets("SheetB").Range(arr2(i) & firstrowDB).Resize(lastrow).Value = .Range(arr1(i) & 1).Resize(lastrow).Value