仅复制粘贴值( xlPasteValues ) [英] Copy Paste Values only( xlPasteValues )

问题描述

我正在尝试将工作表 A 中的整列复制到工作表 B.工作表 A 列的值由公式构成.我仅使用 xlPasteValues 复制 SheetA 列值.但它不会将值粘贴到另一个 sheetB.sheetB 中的列是空的.我的 VBA 代码

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

或

Sub CopyCol()

    Sheets("Sheet1").Columns("A").Copy

    Sheets("Sheet2").Columns("B").PasteSpecial xlPasteValues

End Sub

或者如果你想保持循环

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

这篇关于仅复制粘贴值( xlPasteValues )的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！