Need Before_Save code

[Phil Hageman]

My workbook is named Scorecard, and its worksheets are
entitled Scorecard, Customer, Finance, and Employee.

When the user clicks the Save icon, I want the cursor in
each worksheet to be placed in Cell A1, and the worksheet
named Scorecard to be the the last worksheet changed.

The object is that when a another user first opens the
Workbook, the worksheet named Scorecard comes up first,
and as each of the other worksheets is opened, it shows
the top of the worksheet - thus the force of cursor to
cell A1.

Thanks, Phil

 

[John Wilson]

Phil,

If, as you say, you want a standard set-up when the user
"opens" the workbook, a simple way would be as follows:

Sub Autpen()
Application.ScreenUpdating = False
Worksheets("Employee").Activate
Range("A1").Activate
Worksheets("Customer").Activate
Range("A1").Activate
Worksheets("Finance").Activate
Range("A1").Activate
Worksheets("Scorecard").Activate
Range("A1").Activate
Application.ScreenUpdating = True
End Sub

The above macro, when placed in a regular module, will run whenever
the workbook is opened and insure that "Scorecard" is the active sheet
and that cell "A1" is the active cell on all the sheets.

John

 

[Phil Hageman]

John, Thanks for your reply. I copy/pasted the code into
the "This Workbook" module, but it doesn't work. There is
no error message to hint of the problem either. Is there
something I should look for?
Thanks, Phil

 

[Trevor Shuttleworth]

Or:

Sub Autpen()
Application.ScreenUpdating = False
Worksheets(Array("Employee", "Customer", "Finance", "Scorecard")).Select
Range("A1").Select
Worksheets("Scorecard").Select
Application.ScreenUpdating = True
End Sub

Regards

Trevor

 

[Phil Hageman]

John,

A new wrinkle - this code does indeed put the cursor in
A1, but you cant see cell A1, rather, it still shows
wherever the user left it when closing. How can I get the
screen to show cell A1?

Thanks, Phil

 

[John Wilson]

Trevor,

I need to "think" array more often.
Your way makes it much cleaner and more straightforward.

Thanx,
John

 

[John Wilson]

Phil,

The "ThisWorkbook" module is where the workbook "Event" code
is placed. With the ThisWorkbook module open, click the left
dropdown and select workbook and in the right dropdown, you can
see all of the events.
There are Sheet Modules too for coding the events specific to the
individual sheets.
Regular modules are for general coding for use anywhere in the
workbook.

Having said the above, were you to take the code that I gave
you (actually, use Trevors as it's more concise) and place it
in the Workbook_Open Event, (in the "ThisWorkbook" module,
it would work there too.

Example:

Private Sub Workbook_Open()
' Trevor's code
Application.ScreenUpdating = False
Worksheets(Array("Employee", "Customer", "Finance", "Scorecard")).Select

Range("A1").Select
Worksheets("Scorecard").Select
Application.ScreenUpdating = True
End Sub

John

 

[John Wilson]

Phil,

Trevor's code should cure this.
I used the .Activate as opposed to the .Select
as Trevor did.
That should cure the problem.

John

 

[Phil Hageman]

Trevpr and John,
Used the Array code - it puts the cursor in A1,
and "Scorecard" is the active worksheet, but in all
worksheets, the scroll does not put the document where A1
is visible. To test, I put the cursor at the end of each
worksheet - that is what is visible when opening.

Is there a scroll instruction to use?
Phil

-----Original Message-----
Or:

Sub Autpen()
Application.ScreenUpdating = False
Worksheets(Array

("Employee", "Customer", "Finance", "Scorecard")).Select

 

[Phil Hageman]

Trevpr and John,
Used the Array code - it puts the cursor in A1,
and "Scorecard" is the active worksheet, but in all
worksheets, the scroll does not put the document where A1
is visible. To test, I put the cursor at the end of each
worksheet - that is what is visible when opening.

Is there a scroll instruction to use?
Phil

-----Original Message-----
Or:

Sub Autpen()
Application.ScreenUpdating = False
Worksheets(Array

("Employee", "Customer", "Finance", "Scorecard")).Select

 

[John Wilson]

Phil,

Now I think I see what you're after.

The following is the long way of doing this (I'm sure there's
a more concise way), but it should do what you want:

Sub Autpen()
Application.ScreenUpdating = False
Worksheets("Employee").Activate
Range("A1").Activate
Application.Goto ActiveCell, True
Worksheets("Customer").Activate
Range("A1").Activate
Application.Goto ActiveCell, True
Worksheets("Finance").Activate
Range("A1").Activate
Application.Goto ActiveCell, True
Worksheets("Scorecard").Activate
Range("A1").Activate
Application.Goto ActiveCell, True
Application.ScreenUpdating = True
End Sub

John

 

[Trevor Shuttleworth]

John

a concise way ? ;-) Sure there's better (and probably shorter)

Sub Autpen()
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Select
Application.Goto ws.Range("A1"), True
Next
Worksheets("Scorecard").Select
Application.ScreenUpdating = True
End Sub

Regards

Trevor

 

[John Wilson]

Trevor,

Yep, that'll do it.
Was concentrating on using the sheet names theat the OP was using
but for what he wanted to do, cycling through all sheets the way you
suggest is better.
The only named sheet that needed to be referenced was the one that
he wanted visible all the time.

John

 

[Phil Hageman]

IT WORKS PERFECTLY!!! Thanks guys for taking so much time
with this. One thing about this code - it's so easily
transportable to any workbook. Again, thank you. Phil