Copy Data to another worksheet with dynamic rows

Hi!

Each month, I have to copy the sales report to a consolidated report, and the number of rows of data for each month is different. On the consolidated worksheet, there is a pivot table at the bottom of the page. So I am wondering which formula should I use to make sure there's enough rows to contain the new data, and the old data can be deleted automatically. Also, the pivot table can calculate the data with the new range.

For right now, i have to manually check the rows to make sure the new data don't cover the place where the pivot table is, and also change the source of the pivot table every time to make sure the calculation is correct. Thank you!

When you paste your monthly data into the consolidated report, do you want all previous data to be deleted?
Or is there some criteria (date etc.) that determines which data is to be deleted?

Basically all data from last month need to be deleted, the last column is a vlookup that I don't want to delete the formula. But I also need auto adjust the row so the vlookup will catch all the new data. Thank you!

Ok, it's possible. Could you attach a sample (de-sensitised) workbook please so that I can write the code.
On your reply click "Go Advanced" then scroll down to "Manage Attachments" to add an attachment.

I have attached a sample excel spreadsheet for you to write code on. Thank you!

Had a bit of time to give this a go today. Make a new sheet called "NewData" and paste your new information into there in cell A1 onwards (with no headers).
Then try the following code:

Please Login or Register  to view this content.

Thank you for your help!

My pleasure to help

---

If that takes care of your original question, please select Thread Tools from the menu link above to mark this thread as SOLVED.
To say thanks to the user(s) who contributed towards the solution, you can use the "Add Reputation" button on their helpful post(s).
Thanks!