Merging Multiple Sheets into a Consolidated Sheet


Board Regular
Feb 8, 2008

I've been trying to Consolidate Data from multiple Sheets to another sheet in the Same workbook.
ive hunted around for a while but cant find anything to do this.

all the sheets (8) have the same headings but not all cells would be filled in.

it would be a simple Copy paste, for my Co-Workers it is too difficult to do this.

So...all the Data in Sheet 121 would be copied to Sheet "Consolidated"
then all of Sheet 122 would be copied to just under.

ive tried Formulas but they delete lines Frequently so it finished with 2000 #REF values

Any ideas??



Board Regular
Feb 8, 2008
there is 11 Columns but for any Data some might be blank
so...for the Columns A, B, C, D, E, F, G, H, I, J, K
F, G, H might be blank

but the next line down...B, D, K might be blank.


MrExcel MVP
Dec 9, 2008

This is not (or should not be) difficult, but it is hard to see exactly what you are after, as we cannot see your workbook or "peer over your shoulder" so-to-speak.

You first mention it as simple as a cut and paste, which would seem to infer taking a range at a time over to the Consolidated sheet. But then you mention there could be blank cells; which sort of infers you want to strip away these.

Could you maybe include a before/after example? This might well help.


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...