Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# Working on multiple workshets.

Copper Contributor

# 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.

5 Replies
best response confirmed by Szymeqpl_ (Copper Contributor)
Solution

# Re: Working on multiple workshets.

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.

# Re: Working on multiple workshets.

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

# Re: Working on multiple workshets.

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?

# Re: Working on multiple workshets.

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.

# Re: Working on multiple workshets.

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

Accepted Solutions
best response confirmed by Szymeqpl_ (Copper Contributor)
Solution

# Re: Working on multiple workshets.

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.