VBA: Copy and paste values in next empty row in other sheet

Question

I'm trying to copy a range from "Sheet1" and pasting values, not code, to the next empty row in "Sheet2". I have found this code but it only copys and pastes code:

Sheets("Sheet1").Range("G32:I54").Copy Sheets("Sheet2").Range("A60000").End(xlUp).Offset(2, 0) 'Select Case Sheets("Sheet1").Range("A1") = ""

Can anyone help pasting values and not code?

possible duplicate of [How to copy only values in excel vba from a range?](http://stackoverflow.com/questions/24294923/how-to-copy-only-values-in-excel-vba-from-a-range) — Jean-François Corbett, Sep 10 '14 at 09:40
No, not really because it does not concider placing at first empty cell — user3049442, Sep 10 '14 at 10:16
This is most definitely a duplicate. The answers to that other question are applicable to your case; just substitute your range (`End(xlUp)` and all that jazz) for the other question's `dest`. — Jean-François Corbett, Sep 10 '14 at 10:23

cyboashu · Answer 1 · 2014-09-10T10:06:21.737

2

Sheets("Sheet1").Range("G32:I54").Copy 
Sheets("Sheet2").Range("A60000").End(xlUp).Offset(2, 0).pastespecial paste:=xlPasteFormats
Sheets("Sheet2").Range("A60000").End(xlUp).Offset(2, 0).pastespecial paste:=xlPasteValues
application.cutcopymode=false

edited Sep 10 '14 at 10:06

answered Sep 10 '14 at 09:35

cyboashu

10,196
2
27
46

If I uses this I just get the error 'This operationrequires the merged cells to be identically sized'. Which part of the second line is considering the size of the cells? – user3049442 Sep 10 '14 at 09:49
You need to apply the formats first, I believe. Try now. – cyboashu Sep 10 '14 at 10:06
Still same error occurs. It pastes formats but gives error at line 3 – user3049442 Sep 10 '14 at 10:29

VBA: Copy and paste values in next empty row in other sheet

1 Answers1