Excel worksheet - VBA to copy specific sheets using dynamic array

tiberius70

New Member
Joined
Nov 25, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi & welcome to MrExcel.
What are the names of the two columns in your table?
 
Upvote 0
Ok, how about
VBA Code:
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
 
Upvote 0
Solution
Ok, how about
VBA Code:
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.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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.
 
Upvote 0
Why not just add that sheet name to the table?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top