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

问题描述

我正在尝试将sheetA中的整个列复制到工作表B。sheetA列具有由公式形成的值。我仅使用 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屋！