Macro that activates sheet upon opening of a file

[Jstns]

Hi Guys!

It probably does not get any simpler than this but I would like to ask the following question:

I have a workbook that I would like to select a certain sheet upon opening.

The code I have taken from the internet is:

Please Login or Register  to view this content.

If I manually activate it while I have the workbook open it works! However when I close and open the workbook up again, the macro should activate and select the instructions sheet, however it states "Worksheet does not exist"

Please help me out!

[xlnitwit]

Hi,

I would recommend specifying the workbook as well

Please Login or Register  to view this content.

[kev_]

Check the actual sheet name spelling VERY carefully

[CK76]

Where does your code reside? Is it in ThisWorkbook module? If it is, it should have "Private" in front of Sub (which is added automatically)

Make sure it's in correct module.

0.JPG

Edit: Alternate to what xlnitwit wrote, you can use "Me." in front of Worksheets as well.
Since the code reside on the workbook itself, it will confine it to the workbook context.

Another thing you can do, is to use sheet index (to right of the sheet name/caption) in Project pane.

[Jstns]

It is in this workbook and I have updated it now to:

Please Login or Register  to view this content.

+ It is in this workbook! And it is still not working....

[kev_]

Yes it does
When before you close the attached file, change something and save it with the other sheet active then open it again

[Jstns]

Oh yeah, it wasn't working due to me protecting the workbook. However it does keep saying WorkSheet does not exist upon opening!

[Jstns]

SO this should be able to solve the problem but now it actually does not work. The file needs to be protected upon opening.

Please Login or Register  to view this content.

[Jstns]

So it only works when the workbook is unprotected, so this should work right?

[bakerman2]

Did you try this one ?

Please Login or Register  to view this content.

[Jstns]

Also does not work unfortunately

[kev_]

Rather than trying to open workbook in the correct sheet, save it with the correct sheet activated:

In ThisWorkbook module

Please Login or Register  to view this content.

[xlnitwit]

It should be ThisWorkbook for all items

Please Login or Register  to view this content.

[bakerman2]

I guess it's more a workbook thing because tested it on a protected workbook and works just fine.

protected a woorkbook with this

Please Login or Register  to view this content.

Put this in ThisWorkbook

Please Login or Register  to view this content.

Activated a different sheet and saved and closed.

After reopening desired sheet was selected without any problem.

Not much more we can do i think.

Maybe it's possible to post the workbook involved so we can test ?

[Jstns]

It works! Thank you all very much

[bakerman2]

Glad you got it running.
Thanks for rep+.