Select range of sheets in Excel VBA

Question

I'm trying to select a range of worksheets in an Excel macro, so that they can then be printed.

I want to print a range, i.e. Sheet2-Sheetx, where x is a variable.

I have tried recording a macro to do what I want, but it uses sheet names rather than sheet references, and of course doesn't support variables.

In this example, I selected three sheets, so x=3:

Sheets(Array("Data", "Data (2)", "Data (3)")).Select
ActiveWindow.SelectedSheets.PrintOut preview:=True

I would have thought this would be simple, but can't seem to figure it out.

Thanks in advance.

Vityata Vityata · Accepted Answer · 2018-02-06T09:40:07

.Name is the property that you need to get the Worksheet's names like this:

WorkSheets(Array(Worksheets(1).Name, Worksheets(2).Name, Worksheets(3).Name)).Select

And if you want to do it fancy, with N as a variable, try like this:

Public Sub SelectN()

    Dim N           As Long: N = 2
    Dim cnt         As Long
    Dim arrOfWs     As Variant

    ReDim arrOfWs(N - 1)
    For cnt = 1 To N
        arrOfWs(cnt - 1) = Worksheets(cnt).Name
    Next cnt
    Worksheets(arrOfWs).Select

End Sub

Select range of sheets in Excel VBA

2 Answers