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_a...
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)