toggle open files
 

I have this code to list the sheets in an active workbook:

Sub listSheets()

Dim x As Worksheet

For Each x In Application.Worksheets
ActiveCell.Value = x.Name
ActiveCell.Offset(1, 0).Activate
Next x

End Sub

.... but would like to list sheets in another open book without activating it.

Thanks in anticpation.

toggle open files
 

Use this, or optionally replace the Worksheets collection with the Sheets
collection instead, which also contains Charts (if any are available)

/MP

===============================


Option Explicit

Public Sub ListAllBooksAndSheets()
Dim wb As Workbook
Dim ws As Worksheet

For Each wb In Application.Workbooks
Debug.Print "= Workbook: " & wb.Name
For Each ws In wb.Worksheets
Debug.Print "== Worksheet: " & ws.Name
Next ws
Next wb

End Sub

===============================

"Newbie" wrote:

I have this code to list the sheets in an active workbook:

Sub listSheets()

Dim x As Worksheet

For Each x In Application.Worksheets
ActiveCell.Value = x.Name
ActiveCell.Offset(1, 0).Activate
Next x

End Sub

... but would like to list sheets in another open book without activating it.

Thanks in anticpation.

toggle open files
 

Sub listSheets()
Dim x as Worksheet
Dim file1 As Workbook
Set file1 = Workbooks("Other_Workbook.xls")
For Each x In file1.Worksheets
ActiveCell.Value = x.Name
ActiveCell.Offset(1, 0).Activate
Next x


That should work as long as the other workbook is already open.

Die_Another_Day