Dynamically consolidate multiple worksheets into one worksheet

[ramakrishnavbms]

Hello everyone,
I have about 50 tabs one for each city and i need to update information everyday for each of those cities. I now what the macro to update all the new entries in each of the worksheets into a consolidated Summary sheet. This is dynamic so if 10 rows are filled in the summary tab and if i update the rows in the individual city worksheets the summary tab should paste values from the 11th row and so on
IS there a way to do it. I wold appreciate any help,
Thank you

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

If you want the clear Summary each time.

Please Login or Register  to view this content.

[ramakrishnavbms]

How do I modify so that only rows from A6 are copied because i have headers in each of the worksheets. i have about 50 work sheets. By this code even the headers are getting copied.
Also the macro says this line has an object defined error ws.UsedRange.Offset(1).Copy Sheets("Summary").Range("A6" & Rows.Count).End(3)(2)
Thanks for your help

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[ramakrishnavbms]

Copy method of range class failed is the message when i run the macro....

[ramakrishnavbms]

I have attached the file to make it easier. I have just put 3 cities for simplicity. But i have around 50 cities. Everyday when i add a row in each of the cities, i would need the data to be consolidated into the Summary sheet. So if i start today i should be able to see 30 days of consolidated data for all the cities after a month.
Thank you for your help again

[JOHN H. DAVIS]

How do I modify so that only rows from A6 are copied because i have headers in each of the worksheets. i have about 50 work sheets. By this code even the headers are getting copied.
Also the macro says this line has an object defined error ws.UsedRange.Offset(1).Copy Sheets("Summary").Range("A6" & Rows.Count).End(3)(2)
Thanks for your help

There's no "A6" in the code I provided.

[ramakrishnavbms]

I tried to do that before you sent me the next code. I am using your original code, it still gives me that error

[JOHN H. DAVIS]

Strange thing. I tried to run the code on the attachment you provided and I get a Run-Time-Error 32809. However, I created another workbook and copied your Sheets there while creating another Summary sheet. The code runs with no error. This one has me puzzled.

[ramakrishnavbms]

I have attached the file to make it easier. I have just put 3 cities for simplicity. But i have around 50 cities. Everyday when i add a row in each of the cities, i would need the data to be consolidated into the Summary sheet. So if i start today i should be able to see 30 days of consolidated data for all the cities after a month.
Thank you for your help again.

Is the code you provided same for this logic too ?

[JOHN H. DAVIS]

No if you are entering data for each sheet then after you add your data you could use something like this.

Please Login or Register  to view this content.

However, this does not work on the workbook provided either. It still shows the same error?

[ramakrishnavbms]

I dont know why. The code is not working. I created a new workbook...The macro runs without giving an error but nothing comes up in the Summary Sheet. Its just blank....If i add another row of data in each of the sheets and run the macro again, it still doesnt populate anything. Im confused too

[JOHN H. DAVIS]

Can you attach another file? This is a first for me and this error code. I'd like to try again on a different workbook.

[ramakrishnavbms]

Please find attached. For the last code which i ran , the summary sheets had duplicate entries from the individual worksheets. I would like only the additional rows that i add to populate in the summary sheet
For ex: In the sheet attached there is data from 3 cities until row 11 in the summary sheet. Once i add more data in each of the 3 cities i want the new added data only to populate in the summary sheet from row12. None of the previous data should be pasted in the summary sheet.
I appreciate your help on this..Please bear with me
Thanks

[ramakrishnavbms]

Bumping the post..

Thank you