How can I open the 3rd sheet when I open a file. I ask it this way as
each week the name of the '3rd sheet' changes and thats why I can't
hard code the specific name of the worksheet within worksheet_open


Thanks

Assuming this is always the same sheet, but renamed, check the identity of
the sheet in the VB editor. The Excel objects are initially identified as
Sheet3(Sheet3), but if you rename it the new name appears in brackets
eg Sheet3 (newname).

If the "permanent" sheet number is always the same you can use
Worksheets(3).Activate

Ian

"Sean" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How can I open the 3rd sheet when I open a file. I ask it this way as
> each week the name of the '3rd sheet' changes and thats why I can't
> hard code the specific name of the worksheet within worksheet_open
>
>
> Thanks
>

No Ian, I insert a new sheet each week, but is always the 3rd sheet
from the left (if that makes sense)

Yes, it does make sense.

I've done a bit of digging and found that Sheets(3).Select seems to do what
you want.
Sheets(3).Activate appears to do the same thing, but I don't know what the
difference is between them.

The names as listed in VB editor are shown as Sheet number (name) where the
number is the position of the sheet counting from the left and the name is
the name you give the sheet. They are identical in a new workbook.

Ian

"Sean" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> No Ian, I insert a new sheet each week, but is always the 3rd sheet
> from the left (if that makes sense)
>
>

Ian, on mine, the 3rd sheet from left (which I inserted last Monday)
is Sheet47. When I insert a new sheet in the '3rd position' next
Monday, I'm assuming this will be named Sheet48

Sorry Sean. I'm afraid I'm struggling here.

I've just had a look on Chip Pearson's site and suggest is a variation of
something I found there (see http://www.cpearson.com/Excel/sheetref.htm)

He has a routine under the heading |"Returning The Name Of The First
Worksheet In The Workbook" on that page. In it there is a reference to
Item(1). Changing this to Item(3) seems to return the name of the 3rd sheet.
This you can use in place of hard coding the name.

If that doesn't work, further down the same page he has a routine under the
heading " Getting The Name Of A Sheet By Position Number". This is a more
complex routine, but this may only be because it is more adaptable. It may
also be that the routine above doesn't work in every instance.

Hope this helps.

Ian

"Sean" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Ian, on mine, the 3rd sheet from left (which I inserted last Monday)
> is Sheet47. When I insert a new sheet in the '3rd position' next
> Monday, I'm assuming this will be named Sheet48
>
>

> but this may only be because it is more adaptable. It may also be that the
> routine above doesn't work in every instance.

The "Returning The Name Of The First Sheet" will work under any
circumstances, or at least it does in my testing.




"Ian" <(E-Mail Removed)> wrote in message
news:7r7Vi.5034$(E-Mail Removed)...
> Sorry Sean. I'm afraid I'm struggling here.
>
> I've just had a look on Chip Pearson's site and suggest is a variation of
> something I found there (see http://www.cpearson.com/Excel/sheetref.htm)
>
> He has a routine under the heading |"Returning The Name Of The First
> Worksheet In The Workbook" on that page. In it there is a reference to
> Item(1). Changing this to Item(3) seems to return the name of the 3rd
> sheet. This you can use in place of hard coding the name.
>
> If that doesn't work, further down the same page he has a routine under
> the heading " Getting The Name Of A Sheet By Position Number". This is a
> more complex routine, but this may only be because it is more adaptable.
> It may also be that the routine above doesn't work in every instance.
>
> Hope this helps.
>
> Ian
>
> "Sean" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Ian, on mine, the 3rd sheet from left (which I inserted last Monday)
>> is Sheet47. When I insert a new sheet in the '3rd position' next
>> Monday, I'm assuming this will be named Sheet48
>>
>>
>
>

Sorry, Chip. I meant it may not work in every instance with an item number
greater than 1, not that I've had any problems.

It just seems odd that you have totally different code for finding a
specific sheet name and assumed that using a variable in place of the
hard-coded 1 in the first sheet name code might, in some instances, cause a
problem.

Ian

"Chip Pearson" <(E-Mail Removed)> wrote in message
news:7189568D-64BD-47F4-8F9E-(E-Mail Removed)...
>> but this may only be because it is more adaptable. It may also be that
>> the routine above doesn't work in every instance.
>
> The "Returning The Name Of The First Sheet" will work under any
> circumstances, or at least it does in my testing.
>
>
>
>
> "Ian" <(E-Mail Removed)> wrote in message
> news:7r7Vi.5034$(E-Mail Removed)...
>> Sorry Sean. I'm afraid I'm struggling here.
>>
>> I've just had a look on Chip Pearson's site and suggest is a variation of
>> something I found there (see http://www.cpearson.com/Excel/sheetref.htm)
>>
>> He has a routine under the heading |"Returning The Name Of The First
>> Worksheet In The Workbook" on that page. In it there is a reference to
>> Item(1). Changing this to Item(3) seems to return the name of the 3rd
>> sheet. This you can use in place of hard coding the name.
>>
>> If that doesn't work, further down the same page he has a routine under
>> the heading " Getting The Name Of A Sheet By Position Number". This is a
>> more complex routine, but this may only be because it is more adaptable.
>> It may also be that the routine above doesn't work in every instance.
>>
>> Hope this helps.
>>
>> Ian
>>
>> "Sean" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Ian, on mine, the 3rd sheet from left (which I inserted last Monday)
>>> is Sheet47. When I insert a new sheet in the '3rd position' next
>>> Monday, I'm assuming this will be named Sheet48
>>>
>>>
>>
>>
>

Thanks for your help Ian, will try it tomorrow, but it looks like what
I need

Ian, code below worked perfectly, thanks again


Private Sub Workbook_Open()
Application.ScreenUpdating = False

Sheets.Item(3).Activate

Range("A1").Select

End Sub