Excel worksheet - VBA to copy specific sheets using dynamic array

[tiberius70]

Help please.

I have a workbook with 10 sheets, the first being an index sheet of all the others.
The index worksheet ("Index") contains a table ("T_Index") which is the index of all the other worksheets ("Worksheet_1", "Worksheet_2", etc)
The table (T_Index) has a column which is yes / no and which I want to use to indicate which of the worksheets I copy to a new workbook as a user point in time workbook.
I can create a dynamic array that lists only the names of the worksheets that are indicated with a Yes in the table column mentioned above

I want to be able to run a macro to copy all selected worksheets (the names in the array) to a new workbook.

This will be done on multiple occasions depending on the needs of a particular user (ie one may want sheets 1,2,3,4 and another 1,4,6,7)

Any solutions gratefully received

 

[Fluff]

Hi & welcome to MrExcel.
What are the names of the two columns in your table?

 

[tiberius70]

Hi & welcome to MrExcel.
What are the names of the two columns in your table?

Hi,

column 1 is SheetName
column 2 is YesNo

imaginative I know but it suffices for what it is

 

[Fluff]

Ok, how about

Sub tiberius()
   Dim Ary As Variant
   Ary = Evaluate("transpose(filter(T_index[SheetName],T_index[YesNo]=""Yes""))")
   If IsArray(Ary) Then Sheets(Ary).Copy
End Sub

 

[tiberius70]

Ok, how about

Sub tiberius()
   Dim Ary As Variant
   Ary = Evaluate("transpose(filter(T_index[SheetName],T_index[YesNo]=""Yes""))")
   If IsArray(Ary) Then Sheets(Ary).Copy
End Sub

Nice one, much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[tiberius70]

Additional request

The array of named sheets copies fine but now I want to add a standard Cover Sheet that isn't in the array to the copy (new workbook)
How do I add in a command to copy the additional sheet across?

I can add in sheets("coversheet").copy as an additional line but it only copies the array and not the additional coversheet.
Equally if I put this before the array then it copies the coversheet and nothing else.

 

[Fluff]

Why not just add that sheet name to the table?