Do you know any direct way in Excel VBA to select multiple worksheets already in an array?

Question

I have a piece of code that creates a variable number of copies from a worksheet. Every new copy is set as the next Worksheet type variable in an array:

Dim wsv() as Worksheet
Dim ddf as Integer, i as Integer

'After some processing, ddf will define the number of copies required,
'therefore the size of the array.

'ws is the original worksheet from which the copies will be made

If ddf > 0 then

    ReDim wsv(0 to ddf) as Worksheet
    
    For i = 0 to ddf
        ws.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Set wsv(i) = ThisWorkbook.ActiveSheet
    Next i

End if

I need to select all these new worksheets in the end, in order to export them as a single PDF.

I have used ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select in situations where the quantity/name of the worksheets to select is known, and I was hoping that ThisWorkbook.Sheets(wsv).Select or ThisWorkbook.Sheets(Array(wsv)).Select would work in this particular one, but they don't.

Do you know how can I do this, taking advantage of having all required sheets to select already in an array?

UPDATE:

I needed to use an array of strings instead, since that's what ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select expects. Thanks to @BigBen and @VBasic2008 for your help!

Answer 1

Worksheets Array

• The function createWorksheetsArray is kind of a hybrid. It creates new worksheets like a Sub would do, and it returns the array of worksheet names of the newly created worksheets, like a Function would do.

• The procedure testCreateWorksheetsArray adds three worksheets to the workbook containing this code (ThisWorkbook), returns the three names in an array whose contents are printed to the Immediate window CTRL+G before all three worksheets are deleted 'in one go'.

• Instead of deleting, you will rather do something like this:

  wb.Worksheets(wsv).Select
  ActiveSheet.ExportAsFixedFormat...
  

New Version

Option Explicit

Sub addWorksheetCopies(Sheet As Worksheet, _
                       Optional ByVal NumberOfCopies As Long = 1)
    
    If Sheet Is Nothing Then
        GoTo ProcExit
    End If
    
    If NumberOfCopies < 1 Then
        GoTo ProcExit
    End If
    
    With Sheet
        Dim n As Long
        For n = 1 To NumberOfCopies
            .Copy After:=.Parent.Sheets(.Parent.Sheets.Count)
        Next n
    End With

ProcExit:

End Sub

Function getLastWorkSheetNames(Book As Workbook, _
                               Optional ByVal NumberOfWorksheets As Long) _
         As Variant
    
    Dim wsCounter As Long
    wsCounter = NumberOfWorksheets
    Dim Data As Variant
    ReDim Data(1 To wsCounter)
    Dim shCounter As Long
    shCounter = Book.Sheets.Count
    
    Dim sh As Object
    Do
        Set sh = Book.Sheets(shCounter)
        If sh.Type = xlWorksheet Then
            Data(wsCounter) = sh.Name
            wsCounter = wsCounter - 1
            shCounter = shCounter - 1
        End If
    Loop Until wsCounter = 0
    getLastWorkSheetNames = Data

ProcExit:

End Function

Sub testBoth()
    
    Const NumberOfCopies As Long = 3
    
    ' Define workbook.
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    ' Define Source Worksheet ('ws').
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")
    
    ' Create copies of Source Worksheet.
    addWorksheetCopies ws, NumberOfCopies
    
    ' Write the names of the copies to Worksheets Array ('wsv').
    Dim wsv As Variant
    wsv = getLastWorkSheetNames(wb, NumberOfCopies)
    If IsEmpty(wsv) Then
        GoTo ProcExit
    End If
    
    ' Status:
    ' You have created your copies of worksheet ws,
    ' and the array wsv contains the names of the copied worksheets.
    ' Continue...
    
    ' e.g.
    Dim n As Long
    For n = LBound(wsv) To UBound(wsv)
        Debug.Print wsv(n)
    Next n
    
    Application.DisplayAlerts = False
    wb.Worksheets(wsv).Delete
    Application.DisplayAlerts = True
    
ProcExit:
    
End Sub

Code Smell Version (not recommended)

Function createWorksheetsArray(SourceWorksheet As Worksheet, _
                               Optional ByVal NumberOfCopies As Long = 1) _
         As Variant
    
    If SourceWorksheet Is Nothing Then
        GoTo ProcExit
    End If
    
    If NumberOfCopies < 1 Then
        GoTo ProcExit
    End If
    
    Dim Data As Variant
    ReDim Data(1 To NumberOfCopies)
    With SourceWorksheet
        Dim n As Long
        For n = 1 To NumberOfCopies
            .Copy After:=.Parent.Sheets(.Parent.Sheets.Count)
            Data(n) = .Parent.ActiveSheet.Name
        Next n
    End With

    createWorksheetsArray = Data

ProcExit:

End Function

Sub testCreateWorksheetsArray()
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")
    
    Dim wsv As Variant
    wsv = createWorksheetsArray(ws, 3)
    If IsEmpty(wsv) Then
        GoTo ProcExit
    End If
    
    Dim n As Long
    For n = LBound(wsv) To UBound(wsv)
        Debug.Print wsv(n)
    Next n
    
    Application.DisplayAlerts = False
    wb.Worksheets(wsv).Delete
    Application.DisplayAlerts = True
    
ProcExit:
    
End Sub