Selection of changing array of sheets via table with selections in sheet 'Index'

Hi guys!

I have a workbook made up from these sheets:

- Index
- Sheet1
- Sheet2
- Sheet3
- Sheet4
- Sheet5

In the sheet 'Index' I have a small table (2 colums) which indicates if the sheet has to be selected in the array or not. The user of the sheet selects 'Yes' or 'No', eg:

Sheet name: Print:
- Index No
- Sheet1 Yes
- Sheet2 Yes
- Sheet3 No
- Sheet4 Yes
- Sheet5 No

In this example the sheet array created by the macro should be "Sheet1, Sheet2, Sheet4". In another example it could be "Index, Sheet1, Sheet5", etc.

I want to create a macro that looks at the table (2 colums) in sheet 'Index' to see if it states 'Yes' or 'No' for the selection and then to make the array needed to print.

Regards,

Antoon

Please Login or Register  to view this content.

Thanks but it is ALMOST the right answer. I don't want the sheets to be printed. I would like to have the desired sheets SELECTED as being an array. I then want to combine them into 1 PDF-file (I know the PDF code, I just want the code to make the customized selection based on the table.

Please Login or Register  to view this content.

Attached a simple example sheet. In the sheet 'Index' i've stated which sheets to select for printing or an export, etc. So the outcome should be that multiple sheets are selected.

Try this

Please Login or Register  to view this content.

Make sure the names listed on the Index sheet exactly match the sheet names (In your example workbook, they do not!)

Thanks! It worked as a charm. :-)

One additional question. The code mentioned works perfectly but what if I decide to hide or remove one sheet? So the table (see picture before) will stay intact but the actual sheets don't.

Where do I place an error code to try the following line (meaning: trying to select the next sheet which is not hidden (or maybe it is too)).

Sub SelectSheetsArray()
Dim v As Variant
Dim a As Long
Dim b As Boolean

With Sheets("Index")
v = .Range("A1:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
For a = LBound(v) To UBound(v)
If v(a, 2) = "Yes" Then Sheets(v(a, 1)).Select Replace:=Not b: b = True
Next a
End With
End Sub

Originally Posted by YasserKhalil

Thanks a lot dangelor for this great code ...
Just curious question : Why using boolean variable and using it in this part of Replace:=Not b then to b=True
I have tried the code and put Replace:=False .. And removed this boolean variable and the code worked well too
Is there a trick in using this variable in that way?

That bit of logic stops the active sheet from being added to the array.

Originally Posted by a.vanmil

One additional question. The code mentioned works perfectly but what if I decide to hide or remove one sheet? So the table (see picture before) will stay intact but the actual sheets don't.

Where do I place an error code to try the following line (meaning: trying to select the next sheet which is not hidden (or maybe it is too)).

Sub SelectSheetsArray()
Dim v As Variant
Dim a As Long
Dim b As Boolean

With Sheets("Index")
v = .Range("A1:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
For a = LBound(v) To UBound(v)
If v(a, 2) = "Yes" Then Sheets(v(a, 1)).Select Replace:=Not b: b = True
Next a
End With
End Sub

As long as any sheet name exists and has a Yes, it will run. It ignores any sheet with a No.