Store ActiveWindow.SelectedSheets as an object to refer to later

Question

I'm trying to write a macro that will create a table of contents, listing the name of each of the worksheets currently selected by the user, together with the number of the page on which it starts when printed. I've taken the code from this page and adapted it a little as below. However, when the new worksheet ("Contents") is created, that becomes the active, selected sheet, such that I can no longer use ActiveWindow.SelectedSheets to refer back to the collection of worksheets selected by the user. So I would like to store that information before creating the new sheet. How can I do this? I have tried assigning it to a variable of type `Worksheets` as you can see, but this generates an error message. (I also tried `Collection` but to no avail.) ``` Sub CreateTableOfContents() ' Determine if there is already a Table of Contents ' Assume it is there, and if it is not, it will raise an error ' if the Err system variable is > 0, you know the sheet is not there Dim WST As Worksheet Dim Sel…

Accepted Answer

I just amended your code. Is this what you are trying? Honestly all you had to do was

Change `Dim SelSheets As Worksheets` to `Dim SelSheets` and your original code would have worked :)

```
Option Explicit

Sub CreateTableOfContents()
    Dim WST As Worksheet, S As Worksheet
    Dim SelSheets
    Dim msg As String
    Dim TOCRow As Long, PageCount As Long, ThisPages As Long
    Dim HPages As Long, VPages As Long

Set SelSheets = ActiveWindow.SelectedSheets

On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Contents").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

Set WST = Worksheets.Add(Before:=Worksheets("blankMagnitude"))

With WST
        .Name = "Contents"
        .[A2] = "Table of Contents"
        .[A6] = "Subject"
        .[B6] = "Page(s)"
        .Range("A1:B1").ColumnWidth = Array(36, 12)
    End With

TOCRow = 7: PageCount = 0

msg = "Excel needs to do a print preview to calculate the number of pages." & vbCrLf & "Please dismiss the print preview by clicking close."

MsgBox msg

SelSheets.PrintPreview

For Each S In SelSheets
        With S
            HPages = .HPageBreaks.Count + 1
            VPages = .VPageBreaks.Count + 1
            ThisPages = HPages * VPages

WST.Range("A" & TOCRow).Value = .Name
            WST.Range("B" & TOCRow).NumberFormat = "@"

If ThisPages = 1 Then
                WST.Range("B" & TOCRow).Value = PageCount + 1 & " "
            Else
                WST.Range("B" & TOCRow).Value = PageCount + 1 & " " ' & - " & PageCount + ThisPages
            End If

PageCount = PageCount + ThisPages
            TOCRow = TOCRow + 1
        End With
    Next S
End Sub
```

EDIT: One important thing. It's always good to use OPTION EXPLICIT :)

Store ActiveWindow.SelectedSheets as an object to refer to later

Solution

Problem