Excel VBA Copy Paste Values only

Question

Trying to create a file which can transfer data on a daily basis from one sheet to another. I can get the transfer of data but it copies over the formulas too and all the cells gives a ref error. How can I copy over only the values and not the formulas. I'm new to VBA and any help will be greatly appreciated. Thanks

Tried to use the PasteSpecial code but will not work.

Sub Number1()
'
' Number1 Macro
'
  Set wsCopy = Workbooks("Copy of PLA Failure Results (Macro) Good Test 2.xlsm").Worksheets("Raw Data")
  Set wsDest = Workbooks("Copy of PLA Failure Results (Macro) Good Test 2.xlsm").Worksheets("Stored Data")

  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row


  wsCopy.Range("J2:R" & lCopyLastRow).Copy _
  wsDest.Range("A2" & lDestLastRow)


End Sub

score 1 · Answer 1 · answered Jul 16 '19 at 08:07

A couple things to clean up your code. Declare your workbook once and then you can reference throughout at a later stage, is needed. Additionally I think your error is happening in your Paste line. See below code (not tested):

Sub Number1()

    Dim wbGood As Workbook
    Dim wsCopy As Worksheet, wsDest As Worksheet
    Dim lCopyLastRow As Long, lDestLastRow As Long

    Set wbGood = Workbooks("Copy of PLA Failure Results (Macro) Good Test 2.xlsm")

    Set wsCopy = wbGood.Worksheets("Raw Data")
    Set wsDest = wbGood.Worksheets("Stored Data")

    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

    wsCopy.Range("J2:R" & lCopyLastRow).Copy
    wsDest.Range("A" & lDestLastRow).PasteSpecial xlValues

    Application.CutCopyMode = False

End Sub

AAA · Answer 2 · 2019-07-16T08:17:44.657

0

You don't need to .Copy in this case. You can just assign the .Value of the destination range to be equal to the value of the copy range, and this is faster than copying:

wsDest.Range("A" & lDestLastRow & ":I" & lDestLastRow + lCopyLastRow - 2).Value = _
             wsCopy.Range("J2:R" & lCopyLastRow).Value

edited Jul 16 '19 at 08:17

answered Jul 16 '19 at 08:03

AAA

3,520
1
15
31

score 0 · Accepted Answer · answered Jul 16 '19 at 08:07

Change:

wsCopy.Range("J2:R" & lCopyLastRow).Copy _
  wsDest.Range("A2" & lDestLastRow)

With:

wsCopy.Range("J2:R" & lCopyLastRow).Copy
  wsDest.Range("A" & lDestLastRow).PasteSpecial xlPasteValues

You can Use PasteSpecial for that, and also you are using A2 and lastrow which is Wrong, it should be A & Lastrow

score 0 · Answer 4 · answered Mar 03 '22 at 19:24

0

xlPasteValues didn't work for me for some reason. But the following change worked.

wsDest.Range("A" & lDestLastRow).PasteSpecial -4163

answered Mar 03 '22 at 19:24

Rishav1112

23
4

Excel VBA Copy Paste Values only

4 Answers4