I'm trying to copy entire column in sheetA to Sheet B. sheetA column has values formed with formuls. I'm copying SheetA column values only using xlPasteValues. But it is not paste the values to another sheetB. The column in sheetB is empty. My VBA Code
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)
With Sheets("SheetA")
lastrow = Application.Max(3, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
.Range(.Cells(1, arr1(i)), .Cells(lastrow, arr1(i))).Copy
Sheets("SheetB").Range(arr2(i) & firstrowDB).PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
End Sub
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