Refer to sheet number not name in Macro

[Rufus001]

Hi There.
Please help.
I have a monthly workbook with sheets named on the basis of the week ending date.
Each month the workbook is copied, reset and used as the basis of the next month with the the last week of month one becoming the first week of month two.
This of course means that the sheet names change every month.
I am trying to automate the process so the users dont have to think too much (or have the oportunity to muck it up), but dont know how to refer to the sheets other than using their names.
This wont work as they change all the time!
In a Macro can I refer to sheets by their position rather than name?
Hope this makes sense.
Any help is appreciated.
Thanks
Rufus

 

[Smitty]

Welcome to the Board!

In a Macro can I refer to sheets by their position rather than name?

Sure:

Sheets(1)

HTH,

 

[Rufus001]

OMG - that simple!!

Sometimes it's the simple things in life that make you smile.

Thanks for pointing it out to me - no-one else has yet, Maybe it was just too obvious?

Anyway - thanks again

Rufus

 

[lenze]

You can also use the CodeName!!
See my discussion here.
http://www.mrexcel.com/forum/showpost.php?p=2078095&postcount=6
The problen with IndexName is a user can move a sheet and cause you headaches

lenze
Hi Smitty

 

[Smitty]

I'd give Lenze's CodeName suggestion a thought, especially with an end-user application. You don't want your code breaking because someone wanted to move a sheet.

(Heya buddy!)

 

[Rufus001]

Thanks Guy's

I have gone with Lenze's suggestion and used the CodeName, which works perfectly in my testing - now we have to see when the users ger hold of it.

Although I dont think that they will change the order of the sheets, you just dont know and its best to be safe rather than sorry......

Thanks again.

Rufus

 

[lenze]

BTW, a smart user can change the CodeName, but I doubt your end-users fall into that category!!! LOL

lenze