Working on multiple workshets.

[Szymeqpl_]

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.

[#2 · Accepted solution]

@Szymeqpl_ 

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.

[#3]

@Szymeqpl_ 

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 Sub

With 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)

[#4]

@HansVogelaar 

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?

[#5]

@Szymeqpl_ 

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.

[#6]

Sounds good, just wanted opinion from someone smarter than me. Again thank you.