Consolidate Specific Cell From Each Sheet Within Workbook

Hi,
Basically I have the Main page, I would like to take cell A1 from each of the sheets in the workbook and put in A2, A3 etc on the main sheet. I need this to happen when I copy a new page as well. See attachment, I have 3 data tabs, when I create the 4th data tab "Data (4)", I would like the value from A1 on the Data (4) tab to be placed at A5 on the main tab.

This is a bit of an ugly hack solution but in A2:

Please Login or Register  to view this content.

Then pull down to A100 or whatever to dropfill for the max number of sheets expected / possible.

Note this looks for sheets with that specific naming schema of "Data (n)" where the n is the integer count.

This is a bit of an ugly hack solution but in A2:

Please Login or Register  to view this content.

Then pull down to A100 or whatever to dropfill for the max number of sheets expected / possible.

Note this looks for sheets with that specific naming schema of "Data (n)" where the n is the integer count.