Consolidation of multiple worksheets into Master worksheet

[E5254730]

Hello Excel Experts,

I've got below VBA code from some other forums which helps to consolidate my 3 worksheets into single (Master) worksheet.

Please Login or Register  to view this content.

But this code does not fulfills my overall requirement.

This macro gives a MsgBox if there is already "Master" worksheet. I want this step to avoid.

Instead I'll insert a "Master" worksheet for once and every time I run the macro, the data should get copied to this "Master" sheet. The data should also get copied in the same format, that is all the worksheets.

[nankw83]

Hi E5254730,

How about this ?

Please Login or Register  to view this content.

[E5254730]

Hi,

Apologize, if I was not so clear in my actual requirement.

The provided code, copies the data again and again.

For e.g. I have 3 tabs i.e. Project 1, Project 2, Project 3 and Project 4. I want data to get copied from Project 1, Project 2 and Project 3 only to Master tab.

In each tab there are 100 records, so once we run the macro, 300 records should be in Master tab. But when I rerun the macro again 300 records gets copied, which means duplication of records.

The headers are also not copied to master tab. Copy method should be same in Master worksheet as in the Project tabs.

[nankw83]

Ok, added another line to delete data when re-running the code ...

Please Login or Register  to view this content.

[E5254730]

Getting debug msg as : Run time error 9: Subscript out of range

[nankw83]

I am not able to get the error At which line this error appears ?

[E5254730]

Please Login or Register  to view this content.

[nankw83]

Did the code in post #2 work with you ?

[E5254730]

i've answered that in post #3

[dangelor]

Possibly...

Please Login or Register  to view this content.

[nankw83]

I just wanted to confirm, the reason is that added line of deleting the data of sheet "Master" has nothing to do with the line you're getting error at unless you have made some other changes to the file.

Edit : Did you change the table to range in any of the sheets ?

[E5254730]

Thank You! the above code looks to be working ok.

I'll respond back if I still come across any issues.

Thanks once again for fixing this.

[nankw83]

Glad to help & thanks for the added Rep. Please take a moment to mark this thread as [SOLVED] from the Thread Tools above your first post. In case you run into other issues, you can change the status again

[E5254730]

Sorry to bother you again, but my overall requirement is not yet fulfilled.

As said the last code provided by you merges all the available worksheets into Master worksheet. But I want only specific sheets to get copied to Master sheet.

In below image, I've showed the worksheets available in my workbook. I only want Project 1, Project 2, and Project 3 sheets to get copied to Master sheet.

Attachment 695123

And when I run the last code provided by you, it copies all worksheets to Master sheet. It also copies data from the Pivot sheet to Master, which is not required.

Lastly when the data gets copied to Master sheet, the Pivot sheet should not get affected, but to get auto refreshed.

Note : In Run_Macro tab I've placed a button to run the macro.

[nankw83]

@E5254730, are you referring to my code in post # 4 or to dangelor's code in post # 10 ?

[E5254730]

Post #4 - gives me error at below line, so i was referring the code posted in post #10

Please Login or Register  to view this content.

please note, my data is table format in all the sheets.

[E5254730]

Hi,

I've below code which copies specific tabs to the Master sheet, but I want only once the headers should appear in Master sheet. Now when the run the macro, I'm getting repeated headers.

In attached you can see the headers are repeated on Row 702 and Row 1403.