Copy and pasting dynamically

[Zarlot531]

I know I'm asking a lot of questions, so I'll try to make this my last
one hopefully.

But, how do you copy and paste dynamically?

For example, when preparing to do a pivot table, I have three
worksheets that vary in row length from day to day. I would like to
put all of the data on one sheet obviously so that I can do a pivot
table. But, the row length varies from day to day, so say I am
copying rows from worksheet A to the bottom of the data in worksheet
B. How do you tell VBA to paste it "lower" in the spreadsheet if
there are more rows or higher if there are fewer?

Thanks!

 

[Anant Basant]

Hi,

You can use the following code:

Sub myPT()
Dim destSht As Worksheet
Dim srcSht As Worksheet
Dim NextRow As Long

Set destSht = Sheets("Sheet B")
Set srcSht = Sheets("Sheet A")

NextRow = destSht.Cells(destSht.Rows.Count, 1).End(xlUp).Row + 1

'source sheet has a heading that I have to exclude from copy
srcSht.Cells(1).CurrentRegion.Offset(1).Copy

'I can choose to paste values
destSht.Cells(NextRow, 1).PasteSpecial xlPasteAll
End Sub

Regards
Anant