How to get multiple processed sheets in a workbook using Excel VBA API

Question

There is a way to select multiple Excel sheets, and then perform some actions, such as Print. However, given the book, how do you know which sheets are selected. There is a vba Application-> ActiveSheet property that gives us the current active sheet, but I could not find a way to get multiple sheets for this.

Answer 1

Is this what you want?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim SelectedSheets() As String
    Dim n As Long, i As Long

    n = 0
    For Each ws In ActiveWindow.SelectedSheets
        ReDim Preserve SelectedSheets(n)
        SelectedSheets(n) = ws.Name
        n = n + 1
    Next

    For i = LBound(SelectedSheets) To UBound(SelectedSheets)
        '~~> This will give you the list of selected sheets
        Debug.Print SelectedSheets(i)        
    Next i

    '~~> The collection can also be used as below
    'Sheets(SelectedSheets).Copy
    'Sheets(SelectedSheets).Select   ' e.g., to re-select them later
End Sub

Sid