Get list of Worksheets from all Open Workbooks (Excel 2003 SP1)

Topic

New Reply

I’m looking for a routine that will list all the open workbooks and their worksheets in the current file. I thought it would be relatively easy to generate such a list, but I feel like I’ve run into a brick wall.

Thanks AGAIN for all the great help available here…

Reply | Quote

Replies

Where in the “current file” do you want the list? Do you want it in a worksheet? If so, what sheet and starting in what cell? Do you want to include ALL open workbooks, even hidden workbooks like Personal.xls?

Reply | Quote

If you want the list in columns A:B of the ActiveSheet, and you do not want to include hidden workbooks, then:


Public Sub ListSheets()
Dim oWB As Workbook, oWS As Worksheet, I As Long
I = 0
ActiveSheet.Range("A:B").ClearContents
For Each oWB In Workbooks
For Each oWS In oWB.Sheets
If oWB.Windows(1).Visible = True Then
ActiveSheet.Range("A1").Offset(I, 0).Value = oWB.Name
ActiveSheet.Range("B1").Offset(I, 0).Value = oWS.Name
I = I + 1
End If
Next oWS
Next oWB
ActiveSheet.Range("A1").EntireColumn.AutoFit
ActiveSheet.Range("B1").EntireColumn.AutoFit
End Sub


Reply | Quote

Thank you very much, Legare. This looks exactly like what I’m trying to do.

I will give it a shot and get back to you shortly.

Thanks again.

Reply | Quote

Legare, it worked like a charm, with the small exception that when the last worksheet was reached, the variable oWS produced an error. So I added an ON ERROR line in front of the loop.

Thank you very much for your assistance. The Excel world would be a poorer place without you and your colleagues.

Reply | Quote

Is one of the sheets a “Chart Sheet”?

Change the line to:

For Each oWS In oWB.Worksheets

to make sure you only loop through the worksheet collection which is a subset of the sheets collection.

Or if you want all the sheets (not just the worksheets). keep the line as it is and change the line:

Dim oWB As Workbook, oWS as Variant, I As Long

Steve

Reply | Quote