toggle open files

[Guest]

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.

 

[Guest]

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

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

 

[Die_Another_Day]

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