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

问题描述

我试图将sheetA中的整个列复制到Sheet B. sheetA列中使用formuls形成值。我仅使用 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

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