How do I feed an array of worksheet names into VBA Sheets.Copy?

Question

I have this code that populates an array with worksheet names, given a condition:

Dim lng_Counter As Long    
Dim arr_wks() As Variant

lng_Counter = 1

For Each wks In ThisWorkbook.Worksheets
    If Left(wks.Name, 2) = "Hi" Then
        ReDim Preserve arr_wks(lng_Counter)
        arr_wks(lng_Counter) = wks.Name
        lng_Counter = lng_Counter +1
    End if
Next wks

I would then like copy these worksheets to a new workbook and so I have tried something like this:

Sheets(arr_wks()).Copy

Which isn't working. The only way I can get it to work is to write out:

Sheets(Array(arr_wks(1),arr_Wks(2),...)).Copy

Which isn't useful as the size of the array will change depending on the number of sheets that meet the condition at a given time.

How can I feed the array into a Sheets(arr).Copy type argument?

Answer 1

You end up with an error where the first element (with an index of zero) is empty.

You can fix that with

lng_Counter = 0

, or

ReDim Preserve arr_wks(1 to lng_Counter)

, or

Option Base 1

on top.