Worksheet Numbers and order

[RichTea88]

Good afternoon all,

I have a number of macros in a workbook that create/update/delete sheets. Some are hidden, some very hidden some are open to see.

Sheets 1 to 5 in my excel objects in the VBA project are all sheets I can ignore from most of these macros so generally when I'm trying to loop through the other sheets to update them I'm using a bit of code to count the number of sheets then using something like

Please Login or Register  to view this content.

Even though when I'm creating (and deleting) sheets I make sure it adds them after the existing worksheets it doesn't seem to keep the order of the original five sheets. It's kicking up all sorts of problems with my subs. Is there any way that I can lock these original 5 sheets as sheets 1 to 5 even if they're hidden or veryhidden?

any help or advise why this happens would be greatly appreciated.

Kind Regards,

R

[LJMetzger]

Hi R,

In my experience there are 3 basic methods to access a Worksheet:
a. Numeric order (your method)
b. Code Name (Internal Name assigned by Excel - Difficult for User to Change)
c. Sheet Name (External Name - Easy for User to Change).

My personal preference is c. A large number of professional programmers swear by method b. I usually swear at it. If you choose method b. or c. you can use a 'nested if' or 'select case' construction to exclude specific sheets or to include specific sheets.

Please Login or Register  to view this content.

Lewis

[RichTea88]

Hi LJM, thanks for the info. I think method b is exactly what I'm looking for, something that will stay constant.

Cheers friend!