Consolidation several tabs' data into a consolidation sheet via a loop?

Hi all,

I was hoping someone could help me with some loop code for below consolidation macro.

My workbook has a "Consolidation" tab and several country tabs (all same format). My code consolidates all of the individual country tabs (after a filter is applied), one below the other in the consolidation tab.

Below code works for 2 countries but there will be 20+ country tabs.

I'm hoping someone can tweak my code so it can loop through all the country tabs rather than having to duplicate the code over and over like below.

Please Login or Register  to view this content.

How does this work for you?

EDIT: Added a "Select Case" statement so you can add the names of the tabs you want to copy in case there are more tabs than just country tabs. This also makes sure the "Consolidation" tab is not included (if your name's not down, you're not coming in!)

Please Login or Register  to view this content.

I seem to remember something about having to activate a sheet before the filter works properly when looping through sheets. Just insert

Please Login or Register  to view this content.

into the line above where LR is calculated, within the case in my code see if that works.

Also, the code you pasted back is still working on DEN instead of wSheet.

Originally Posted by Gti182

thanks kadeo and John both work great!

A pleasure!