CODEWITHSUNDEEP
excelvba
user3407676
user3407676

Reputation: 11

Range Row pasting in Second row instead of First

I have written some VBA to copy data from one sheet to another. The data is added to a table an then into a Pivot Table report. I am coping the data as rows in a range so I can use the PasteSpecial xlPasteValues function. The data copies and paste find except my paste location adds a blank row at the top of the new data. I think the issue is the .Offset(1,0) in my code.

shtNew.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

How can I find the size of the range on shtNew, paste in the first row and then in paste in a new row after the first paste? Do I need a IF/Then?

Upvotes: 1

Views: 1701

Answers (1)

sten
sten

Reputation: 380

Try 

.Range("A1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues

*corrected my nonsensical explanation. If that is leaving you a space then it means you're including a space in your copied range. Just tested it.

Upvotes: 2

Related Questions