VBA Copy and pastevalues in another sheet using offset

Question

I want to create an excel with a macro which does this: It takes a values from one sheet and paste them in another sheet in the first empty Row. I currently have this and this does paste but then the formula, not the values. When I change Paste to Pastespecial it does not work anymore. Can anybody help me with this?

Sub CopyPasteSpecial()
Set wsA = Sheet3
Set wsW = Sheet5
wsA.Range("A3").Copy


Sheet5.Activate
  Dim i As Integer
  Application.ScreenUpdating = False
  NumRows = Range("C5", Range("C5").End(xlDown)).Rows.Count
  Range("C5").Select
  For i = 1 To NumRows
     ActiveCell.Offset(1, 0).Select
  Next
  Application.ScreenUpdating = True
  ActiveSheet.Paste

score 1 · Answer 1 · answered Oct 09 '22 at 17:55

The (accepted) answer of Sachin Kohli contains so many problems that it is too much to write in a comment. All of them causes errors that can be found thousend times on Stackoverflow.

(a) Don´t use Select and Activate - https://stackoverflow.com/a/10718179/7599798 - You have already a worksheet variable to sheet5 (wsW), so use it.
(b) Copying data (values) between ranges (=cells) can be done without Copy&Paste. This is much faster.
(c) When dealing with row and column numbers, use datatype Long, else you risk an overflow error in large sheets. Basically, use always Long instead of Integer - the data type Integer exists only for historic reasons.
(d) Always put Optiona Explicit at the top of your code and declare all variables. This will save you a lot of headaches because typos in variable names will be caught by the compiler.

Sub CopyPasteSpecial()
    Dim wsA As Worksheet, wsW As Worksheet
    Set wsW = ThisWorkbook.Sheets("Sheet2")
    Set wsA = Sheet1
    Set wsW = Sheet2

    Dim lastRow As Long
    lastRow = wsW.Cells(wsW.Rows.Count, "C").End(xlUp).Row
    wsW.Cells(lastRow + 1, "C").Value = wsA.Range("A3").Value
End Sub

Thanks for pointing out the issues in your answer @funthomas... & to be honest, I was aware of all these points which you mentioned in your detailed answer, however I just focused on tweaking the code for only the part where OP was struggling... — Sachin Kohli, Oct 10 '22 at 04:03

score -1 · Accepted Answer · answered Oct 09 '22 at 16:59

Try this below code for pasting as values & no need to iterate to get to the last empty row as well...

Sub CopyPasteSpecial()
Set wsA = Sheet3
Set wsW = Sheet5
wsA.Range("A3").Copy

Sheet5.Activate
Dim i As Integer
Application.ScreenUpdating = False
NumRows = Range("C5", Range("C5").End(xlDown)).Rows.Count
Range("C" & NumRows + 5).Select

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

Hope this Helps...

Thank you very much! It does indeed work, great idea. This help me very much — wiebrand, Oct 09 '22 at 17:11

VBA Copy and pastevalues in another sheet using offset

2 Answers2