Suggestions to paste new data on last row....

[knightwriter]

Hi all,

The macro I run extracts data from multiple workbooks and I have set different ranges from where to copy & paste this data into one data sheet.

Issue I am having is that when I run the macro it replaces the data from previous extract in my data dump sheet. Is there a way I can keep the previous data and put the next data extract on the last row?

I have used: last_row = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1").Copy Destination:=Cells(last_row + 1, "A")

but this does not recognize where the first workbook needs to start pasting values, its just takes the cell from A1.

All help much appreciated

 

[steve the fish]

Looks like its to do with you not qualifying your sheets. Which sheet are you in when the macro is run? Which sheet is the data to be pasted in?

 

[knightwriter]

I go from an "instructions" tab where the specific source workbooks are listed and want it to paste to a "extracted data" tab.

 

[steve the fish]

You need to qualify them sheets. You cant just use cells or excel will use the activesheet at the time. That may or may not lead to the correct number. Like this for example:

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")

last_row = sh1.Cells(Rows.Count, "A").End(xlUp).Row
sh2.Range("A1").Copy Destination:=sh1.Cells(last_row + 1, "A")