Macro to merge multiple sheets into one

Hello Folks

I am in need of a macro to consolidate multiple sheets into one sheet with following conditions,

1) The header labels need to be copied over to the new sheet.
2) All data from individual sheets except the last row (Total Row) needs to be copied over to the newsheet.
3) The last column after transfer of data needs to be populated with the Respective Sheet names.

Attached is an example with requirement described.

Appreciate any help.

Thanks
Kris

Hi Robert,


Thanks for your reply. The Code debugs @ 3rd line after Each Statement ie
LngPasteRow =- Sheets("Merged Data"). This is because the code assumes that there would be an existing sheet named Merged Data
to which it is trying to copy. In the example I provided, I had manually done the consolidation to showcase of what I wanted. I want the macro to create a new sheet with all the consolidated data being copied over. Also, the column range A&D in my attached example is only for illustration purpose,
but in my real task , the no of columns may vary , however it would be the same across all the sheets.

Can you please suggest a revised code with the above points being taken care.

Thanks
Kris

Perfect mate! Thanks heaps guys.

Hi xlbiznes

I just need to change a little bit different approach to what I initially requested.
Basically, I would keep the merged data sheet always and will copy over the source sheets every month.
Is it possible to include a macro code, to first erase the contents of columns A to Z of Merged Sheets first and then
combine the source sheets.

On a separate note, I just need a code to paste special a column to divide by a constant number say 3. To illustrate say Column C
has Average of 3 months data and I just need to convert it into a monthly average and so I need a macro to copy and paste special C
with Values in Columns C divided by 3.

Thanks for your help.

Awesome Xlbiznes. Thank you very much.

No worries. Good Night

Hi Xlbiznes

I just experienced that I have added one more sheet and have hidden that worksheet , however the macro
is merging the data from the hidden sheet as well.This hidden sheet is required as some of the data is flowing into the Merged Data sheet.
Can you make your code to merge only visible sheets into the "Merged Data Sheet".

Sorry for being inconvenient.

Regards
Kris

Hi Xlbiznes,

I have found the solution for the converting the average. please discard this request.
However would you be able to help me on the Original request for Merging Data. Only the visible sheets should be copied into the Merged data.
Appreciate if you can suggest a code which I can add to your original solution.

Thanks

I have got the solution for monthly average. Please discard this request.
Appreciate if you can tweak your original solutions for merging sheets but need only for visible sheets only.

Thanks

Sorry checking it now

Thanks a lot my friend.

Hi Trebor

Can you help me to add a line to ignore two sheets which holds pivot table before
merging visible sheets into the "Merged Data" sheet.
Below is my current code which works fine.

HTML Code: 

Hi Robert

Though the code ignores the pivot table sheet but after the code is run,
the pivot table disappears from the sheet. I have got a dynamic named range for the pivot.
Not sure why pivot table gets deleted.