Values from mulitple Tabs

[jrisebo]

I have a spreadsheet with multiple tabs (about 30). I want to pull and make a table or condense into a column on one tab. The cell will always be the same, lets say A1.

How can I do this with code, regardless what the tab names are?

 

[Fluff]

Did you try the formula I suggested?

 

[smozgur]

Thanks all. I used a macro to list all my tabs, then =INDIRECT("'"&A2&"'!A1") for each tab to pull A1.

You are using 365 and no other Office version is specified in the original question. The solution provided in post #6 is the best solution to the original question and it will surely help future readers better.

Therefore, I switched the marked solution accordingly.

 

[SanjayGMusafir]

@jrisebo how about using vstack like

=VSTACK(Sheet1:Sheet6!A1)

@jrisebo Even I was wondering why you opted Macro route when you have got such an easy solution in hand... Good you gave it a try...