Macro to select sheets in a dynamic list (Named) containing all the sheets' names

[akakak]

Hi All

My workbook has sheets named from A to Z and a Master-sheet with values auto-populated from sheets A-Z. A dynamic list on Mastersheet updates itself based on which sheets have values greater than Zero. That dynamic list only has the letters A,B,C - Z (excludes tabs which have total value of "0")

I would like a macro able to select only the sheets in that dynamic validation list. The list is named "PdfPrintOfTabsWithData".

Would someone be so kind to let me have a macro that selects sheets based on info on a named list?

Best

AKAKAK

 

[Andrew Fergus]

Hello and welcome to MrExcel

Try the following code:

Sub SelectSheets()

Dim shtNames As Variant

With ThisWorkbook
    shtNames = .Sheets("Master").Range("PdfPrintOfTabsWithData")
    shtNames = Application.WorksheetFunction.Transpose(shtNames)
    .Sheets(shtNames).Select
End With

End Sub

Change the master sheet name that I have used "Master" to the actual name of your master sheet.

I trust this helps
Andrew

 

[akakak]

Hi there

Thanks for the reply, however I got this error:

Run-time error '1004': Application-defined or object-defined error. If clicked on Debug the following line is in yellow:

shtNames = .Sheets("Master").Range("PdfPrintOfTabsWithData")

Please could you take a look again!

 

[Andrew Fergus]

Hi

The macro assumes there is a sheet called "Master" and on that sheet there is a named range called "PdfPrintOfTabsWithData".

If that is not quite right, then either change the sheet name/named range to suit or amend the macro so that it has the same names that you currently have.

Andrew