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) & firstrowDB).PasteSpecial xlPasteValues
        End With
    Next
    Application.CutCopyMode = False

End Sub

Have you tried stepping through the code with the main window alongside? Breaking down the code may help find your error as well. Maybe add some `.Select`'s to it while stepping through to try and pinpoint whats happening, but as there doesnt seem to be anything stopping the code from working as far as I can see after running it myself, theres not much else I can suggest. — bmgh1985, May 29 '14 at 15:56
[Using The Transfer Method](https://www.thespreadsheetguru.com/the-code-vault/best-way-to-copy-pastespecial-values-only-with-vba) helped me when I was getting #N/A values on a regular copy paste — William Humphries, Mar 06 '20 at 20:32

score 58 · Accepted Answer · answered May 29 '14 at 16:01

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

I'd avoid using clipboard for automation whenever possible. If the user or another application uses the clipboard at the same time, the data might end up in unexpected places. This could cause data loss or even a leak of classified information. — jumxozizi, Feb 02 '16 at 15:34

robotik · Answer 2 · 2021-10-19T09:20:59.737

18

since you only want values copied, you can pass the values of arr1 directly to arr2 and avoid copy/paste.

code inside the For loop, inside the With block, after lastrow calculation:

Sheets("SheetB").Range(arr2(i) & firstrowDB).Resize(lastrow).Value = .Range(arr1(i) & 1).Resize(lastrow).Value

edited Oct 19 '21 at 09:20

answered Jan 22 '15 at 23:08

robotik

1,837
1
20
26

1

This should be the answer. – wotter May 26 '20 at 12:47

score 2 · Answer 3 · answered May 29 '14 at 16:02

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

score 2 · Answer 4 · edited Feb 19 '16 at 05:11

2

you may use this:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

edited Feb 19 '16 at 05:11

Davesexcel

6,896
2
27
42

answered May 21 '15 at 12:44

akallali

29
5

score 1 · Answer 5 · answered Jul 21 '17 at 07:13

1

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

answered Jul 21 '17 at 07:13

nishit dey

458
1
7
21

score 1 · Answer 6 · answered Jul 30 '18 at 23:22

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.

score -2 · Answer 7 · answered Jan 04 '18 at 05:10

-2

selection=selection.values

this do things at a very fast way.

answered Jan 04 '18 at 05:10

HARSHUL

1

2

Please clarify how this answers the original question? – roelofs Jan 04 '18 at 05:39
1

That is an interesting way to override data in a selection with values only, but doesn't really address the asked question. – OSUZorba Feb 22 '20 at 05:19

Copy Paste Values only( xlPasteValues )

7 Answers7

Linked

Related