excel - 仅Excel VBA复制粘贴值（xlPasteValues）(Excel VBA 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.

(我正在尝试将sheetA中的整个列复制到工作表B。sheetA列具有由公式形成的值。)

I'm copying SheetA column values only using xlPasteValues .

(我仅使用xlPasteValues复制SheetA列值。)

But it is not paste the values to another sheetB.

(但这不是将值粘贴到另一个sheetB。)

The column in sheetB is empty.

(sheetB中的列为空。)

My VBA Code

(我的VBA代码)

    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

  ask by sam translate from so

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