Select sheets in workbook then consolidate data

Hi friends,

* I have a workbook, with different sheets (with sheet name as Home, RawData, Data1, Data2, Data3, ...), these sheets are not fixed
* FYI... sheet (home) has a VBA code which on running creates a new sheet and pull data from some other source (So the sheet number is not fixed in the workbook, but the newly added sheet is named in a specific format as Data1, Data2, Data3, ...)
* So we can say, that the sheet number and name are not fixed (It keeps on changing)
* All these sheets have data in the same format starting from range A2:J2, except sheet(home)

Two things i am trying to do:-
- Select sheets from the workbook (to be consolidated)
- Consolidate selected sheets in a new sheet

Here is the sample file Select sheet then consolidate.xlsm


Thanks & regards,
Manish

Yes Norie, I want to consolidate data from sheet name starting with Data, but not from all the sheets which starts with Data.
I want to first choose the sheets then consolidate the data by adding a new sheet in the same workbook.

Like may be the first user will select only sheets (Data1, Data3 & Data4) and then he will consolidate the data
then second user may select Sheets (Data3, Data4 & Data5) and then he will consolidate the data

Sorry I forgot about that,

First row of consolidate sheet will contain the header and the following rows will contain the data from the first sheet, then immediately the next row will contain the data from the next selected sheet without the header and so on.

Hope I am able to explain it correctly

Wow... Lovely... it is exactly how i wanted it to be

If you dont mind can i ask you one more help. Is there a way to select the sheets by using userform may be a listbox. If yes then can you please help me with that?

Also can you please tell me about some of the variables you have used in the code
Dim arrMaster()
Dim arr()
Dim idx As Long
Dim I As Long

Norie Once again thanks a lot for your help

Originally Posted by Norie

This will consolidate all the currently selected sheets whose name begins with 'Data'.

It will take the header row from the first of those sheets.

Please Login or Register  to view this content.

Note I've not checked the results as that's kind of hard to do when there's no static data.