Generate dynamic list of Sheets

[llamafarmer]

Hi All,

I am trying to get some code to essentially generate a list of the Worksheets within the same Workbook. I would like to the list to be populated starting from Cell B20 of the sheet named Home.

The home sheet will always be the first sheet, there are two hidden sheets after that which will always be the next 2 sheets and I would like them excluded from the list. There will be no other hidden sheets so it could simply list all the visible sheets excluding the home if that is easier to do.

Thank you in advance.

[alansidman]

What are the names of the hidden sheets?

[llamafarmer]

Data
Template

[alansidman]

Please Login or Register  to view this content.

EDIT: Corrected Or to And

[llamafarmer]

Wow that was quick!

Is there anyway to make it auto update?

Works perfectly, although it still lists the Home sheet (which isn't really a major issue.

Thank you again.

EDIT: Just realised that the attached file has the first edit of code.

[bakerman2]

Small ammendment.

Please Login or Register  to view this content.

[alansidman]

@Bakerman


I've never seen this convention before. I clearly understand what you are doing, but where do I learn about this convention. Can't seem to find anything on the Inter Web.

Please Login or Register  to view this content.

[Rick Rothstein]

I don't think what bakerman2 used is so much a convention as it is simply an understanding that logical expressions have numeric equivalents that can be manipulated mathematically and that any non-zero number is considered as being True by the If statement. I would imagine that you might have come across this type of usage in array formulas where one might want to avoid using the AND function.

[Rick Rothstein]

Here is another macro that can be considered...

Please Login or Register  to view this content.

[llamafarmer]

Thank you all for your help.

I've added the script to an existing Macro which makes it all work "dynamically" perfect now.

What I love about VBA is how there are always multiple approaches to the same problem, thank you again.

[alansidman]

@Rick

Thanks for the explanation.

Alan