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   amp  firstrowDB  PasteSpecial xlPasteValues         End With     Next     Application CutCopyMode   False  End Sub

User · Accepted Answer

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

User · Answer

since you only want values copied  you can pass the values of arr1 directly to arr2 and avoid copy paste  code inside the loop          Sheets  SheetB   Range arr2 i   amp  firstrowDB  Resize lastrow  1  Value    Range  Cells 1  arr1 i     Cells lastrow  arr1 i    Value

User · Answer

You may use this too  Sub CopyPaste   Sheet1 Range  A A   Copy  Sheet2 Activate col   1 Do Until Sheet2 Cells 1  col           col   col   1 Loop  Sheet2 Cells 1  col  PasteSpecial xlPasteValues End Sub

User · Answer

Personally  I would shorten it a touch too if all you need is the columns   For i   LBound arr1  To UBound arr1      Sheets  SheetA   Columns arr1 i   Copy     Sheets  SheetB   Columns arr2 i   PasteSpecial xlPasteValues     Application CutCopyMode   False Next   as from this code snippet  there isnt much point in lastrow or firstrowDB

User · Answer

you may use this   Selection PasteSpecial Paste  xlPasteValues  Operation  xlNone  SkipBlanks             False  Transpose  False

User · Answer

I ve had this problem before too and I think I found the answer   If you are using a button to run the macro  it is likely linked to a different macro  perhaps a save as version of what you are currently working in and you might not even realize it  Try running the macro directly from VBA  F5  instead of running it with the button  My guess is that will work  You just have to reassign the macro on the button to the one you actually want to run

User · Answer

selection selection values  this do things at a very fast way

[excel] Copy Paste Values only( xlPasteValues )

Examples related to excel

Examples related to vba

Examples related to copy-paste