Copy Paste Values only( xlPasteValues )

Question

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 

Answer 1

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 

Answer 2

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