VBA helps needed to consolidate data from multiple sheets by matching Column header

Hi Folks,

I have several sheets in my Workbook. All have matched columns headers but in different order.
I need to copy all the row data from all sheets to “Consolidated_Data” sheet. Is there any way to copy the data exactly under matching column headers?

My small example workbook can describe better.


Thanks in advance for your time.

Joshi

Hi Nilem,

Thank you so much, its working great. Some minor issues, if I set only a single column header in "Consolidated_Data" sheet then the code giving debug message (see the attached file). Also I want to clear all format if the column header is blank.

Can you please check and help me to fix the issues.

Thanks in advance.

Hi,

Try the attached.

It uses the following macro with the helper row on the consolidated sheet

Please Login or Register  to view this content.

Hi Richard Buttrey,

Thank you so much for your time and advice. Still some issues, in your codes the data is copying with row header which I doesn't required. And not comfort with the helper row (formula) on the consolidated sheet since I have to edit all the times if I add more sheets. I prefer Nilem's code with slight modification which I requested in my first reply. Could you please help me to fix the issue.

Thanks in advance,
Joshi

Hi,

You may be mistaken. There's no need to change the helper row just because you add new sheets.
Actually Nilem's approach and mine are very similar. Whereas he holds the column labels in a VBA array, I chose to use the helper row approach.

I left the row header in place so that you could satisfy yourself that it was consolidating correctly. Just change the macro to compensate if you don;t want it, or filter the consolidated data for a row header and delete the filtered rows.

Hi Nilem,
Thank you so much for your time and help. But now its only working for single column header. if I set more than one column headers its giving debug message.

Please help me to fix this issue.

Thanks in advance.
Joshi

Originally Posted by krjoshi

Hi Nilem,
...

Please help me to fix this issue.
Joshi

Just as a matter of interest have you tried the solution I gave you which is similar to Nilem's or does that result in the same problem?

Hi Richard,

I have tried your codes and working great. But I don't want the formula (helper row) in the main sheet, because if I clear the formula by mistake then the code will give debug message. Also I don't want repeat column headers of the each sheets.

Can you please help me to fix these issues.

Thanks for your time and help.

Joshi

Hi,

In that case I'll leave Nilem to respond since his code works in a similar way. He uses VBA to determine which column in each sheet is the appropriate column whereas my version uses Excel to determine the column.

(Incidentally the helper row could be hidden or even located on another sheet if you really don't want to see it, and of course as I said the duplicate column headers can easily be removed)

But as I say I'll let Nilem respond to his version.

Hi Nilem,

Ohh great code. And working excellent. Can you please do one more favor, if I select column header same as to all columns (i.e Column "A" as "Unit" , "B" as "Unit", "C" as "Unit" I want to paste all values as same), Please help me.

Thanks,
Joshi