Forum Discussion
Working on multiple workshets.
Good morning,
I'm facing an issue while working with multiple worksheets.
I'm working with approximately 1056 worksheets, all of which have very similar names, such as "OM50_Txx_fyy_pzz_qnn_amm," where "xx," "yy," "zz," "nn," and "mm" change. My goal is to retrieve the value in cell "B5" from every worksheet in my Excel workbook. I've been advised to use the INDIRECT function, but I'm having trouble understanding how to use it correctly.
I would greatly appreciate any tips or assistance on how to accomplish this.
Thank you.
Create a list of all worksheet names on a separate sheet. See How to Get All Worksheet Names in Excel (2 Easy Ways) for two different methods to do that.
Let's say you have the worksheet names in B2 and down.
In C2, enter the formula
=INDIRECT("'"&B2&"'!B5")Fill down.
Sub ListSheets() Dim ws As Worksheet Dim x As Integer x = 1 Sheets("Tabelle1").Range("C:C").Clear For Each ws In Worksheets Sheets("Tabelle1").Cells(x, 3) = ws.Name x = x + 1 Next ws End SubWith this code you can list all worksheet names in column C (starting in cell C1) of a certain sheet. In this example it is sheet "Tabelle1". You can replace "Tabelle1" with the name of the sheet where you want to return the result.
=INDIRECT("'"&C1&"'!B5")Then you can enter this formula in cell D1 and fill it down.
Here is the source of the VBA code:
Macro to List all Sheets in a Workbook - VBA Code Examples (automateexcel.com)
Create a list of all worksheet names on a separate sheet. See How to Get All Worksheet Names in Excel (2 Easy Ways) for two different methods to do that.
Let's say you have the worksheet names in B2 and down.
In C2, enter the formula
=INDIRECT("'"&B2&"'!B5")Fill down.
Alright I really appreciate that, I found it helpful.
Nevertheless, I'd like to hear your opinion on the formula someone told me to use:
Of course it's not a finished formula but doesn't it makes the formula to go to the specific worksheet in my workbook?
At the very least, the formula should begin with
=INDIRECT("'sr_OM50_T...
If the 50 is also variable, it becomes even more complicated.
I think the methods proposed in my and OliverScheurich's replies are less complicated.
