Automatically go to Worksheet upon opening

Michael M

Well-known Member

Joined

Oct 27, 2005

Messages

21,895

Office Version

1. 365
2. 2019
3. 2013
4. 2007

Platform

1. Windows

• May 4, 2011

• #2

Hi
One line is all you need

Code:

Private Sub Workbook_Open()
Sheets("Main Menu").Range("A1").Activate
End Sub

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• May 4, 2011

• #3

Michael M said:

Hi
One line is all you need

Code:

Private Sub Workbook_Open()
Sheets("Main Menu").Range("A1").Activate
End Sub

Click to expand...

I think that will fail if Main Menu is not already the active sheet.

Try

Code:

Private Sub Workbook_Open()
Application.Goto Sheets("Main Menu").Range("A1"), True
End Sub

 

Upvote 0

T

Toonloon

Board Regular

Joined

Dec 13, 2005

Messages

98

• May 4, 2011

• #4

Thanks for the replies lads,

Michael... i tried a lot of different permutations of the code, 1 line, 2 lines etc... but still got the error

However.....

I have not tried VoG's code..... so... here's fingers crossed....i probably won't find out for a while

Thanks a mill to both of ye

 

Upvote 0

T

Toonloon

Board Regular

Joined

Dec 13, 2005

Messages

98

• May 4, 2011

• #5

Ah Well...

Just tried it out and got the error again.

Would the fact that the Workbook is shared out cause the error ?

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• May 4, 2011

• #6

Toonloon said:

Would the fact that the Workbook is shared out cause the error ?

Click to expand...

Almost certainly. You could avoid the error like this

Code:

Private Sub Workbook_Open()
On Error Resume Next
Application.Goto Sheets("Main Menu").Range("A1"), True
On Error GoTo 0
End Sub

 

Upvote 0

K

kidwispa

Active Member

Joined

Mar 7, 2011

Messages

330

• May 4, 2011

• #7

Sorry to jump on the back of this thread but thought that was better than starting a new thread as it relates to the same thing. I've used the code suggested by VoG above and it works like a treat, however upon opening I would also like to run a macro. Can you please expand on the code suggested so I can incorporate this too? The macro is called "PivotSec1".

Thanks

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• May 4, 2011

• #8

Try

Code:

Private Sub Workbook_Open()
Application.Goto Sheets("Main Menu").Range("A1"), True
Call PivotSec1
End Sub

 

Upvote 0

K

kidwispa

Active Member

Joined

Mar 7, 2011

Messages

330

• May 4, 2011

• #9

Hi VoG,

Thanks for speedy response, however I still get an error message. Basically I've got a pivot chart on the page that I want to open, however the page is password protected and I get an error message come up that says:

"That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data"?

The macro I created was to unprotect the sheet, select the "use pivottable reports" option (as for some reason this doesn't save) then reprotect the sheet.

Any ideas how I can bypass this error message?

 

Upvote 0

T

Toonloon

Board Regular

Joined

Dec 13, 2005

Messages

98

• May 4, 2011

• #10

VoG...you're a LEGEND
All working ok

Thanks a mill

 

Upvote 0