Rename Wooksheet via VBA

[Roosters]

Here is my problem, I have data sent to me in an Excel workbook at the end of each month (large company) with safety stats for the whole company, from this I extract data to my spreadsheet at the press of a button. I have code written that works fine untill the monthly spreadsheet sent to me has different worksheet names to the original I am usually sent (slight differnece only). Is there a way to rename the woorksheet names using code before I run my update code?
ie
Windows("Safety Stats Master.XLS").Activate
(then rename the woorksheet names by selecting the 5th, 6th and 7th worksheets in this wookbook and nameing them as I wish) - now I think about it what I need to know is how to select a wooksheet in a workbook without knowing it's name ie its position?

Wow thats a big question (sorry)
Phil

 

[SkipVought]

Phil

Are the new sheet names always the same?

If so, build a table to map the new names to the correct sheet.

Then do a lookup when processing monthly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Roosters]

Hi Skip,

The sheet names with the latest data change some months and that is my problem - if they were uniform I would not have any problem - the position of the sheets remain constant so I was wondering if the sheet can de selected by its position, not it's name?
Phil

 

[ettienne]

Yes you can use the sheet number instead of the sheet name.

 

[SkipVought]

sheets(1).name = "FirstSheet"
Sheets(sheets.count).name = "LastSheet"

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]