Save Macro
 

I'd like a macro that, prior to saving the current workbook, would position
the cursor in cell A1, and make the first sheet the active one. The
following code does not work. Can anyone help?

Public Sub SaveWorksheet()
Dim wrksht As Worksheet
For Each wrksht In ActiveWorkbook.Worksheets
Range("A1").Select
Next wrksht
Worksheets(1).Select
ActiveWorkbook.Save
End Sub

Sprinks

Save Macro
 

Use the before save event for the thisworkbook object/module

http://www.cpearson.com/excel/events.htm

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sh as Worksheet
ThisWorkbook.Activate
for each sh in thisworkbook.Worksheets
sh.Activate
sh.Range("A1").Select
Next
thisworkbook.worksheets(1).Select
Cancel = True
On Error goto ErrHandler
Application.EnableEvents = False
ThisWorkbook.Save
ErrHandler:
Application.EnableEvents = True
End Sub

This must be in the ThisWorkbook Module.

--
Regards,
Tom Ogilvy

"Sprinks" wrote in message
...
I'd like a macro that, prior to saving the current workbook, would
position
the cursor in cell A1, and make the first sheet the active one. The
following code does not work. Can anyone help?

Public Sub SaveWorksheet()
Dim wrksht As Worksheet
For Each wrksht In ActiveWorkbook.Worksheets
Range("A1").Select
Next wrksht
Worksheets(1).Select
ActiveWorkbook.Save
End Sub

Sprinks

Save Macro
 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wrksht As Worksheet
For Each wrksht In ActiveWorkbook.Worksheets
wrksht.Range("A1").Select
Next wrksht
Worksheets(1).Select
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sprinks" wrote in message
...
I'd like a macro that, prior to saving the current workbook, would
position
the cursor in cell A1, and make the first sheet the active one. The
following code does not work. Can anyone help?

Public Sub SaveWorksheet()
Dim wrksht As Worksheet
For Each wrksht In ActiveWorkbook.Worksheets
Range("A1").Select
Next wrksht
Worksheets(1).Select
ActiveWorkbook.Save
End Sub

Sprinks

Save Macro
 

Thanks, Tom. I was not activating the sheet first. I don't want to include
the code in a BeforeSave event, however, both because I'd prefer to keep all
code in one central file, and because I don't want to trigger the event on
every periodic save, just when you're ready to save prior to closing the
file. At other times, we'd prefer to press the Save icon, and continue
working where we are. The following works as expected. Thank you for your
quick and accurate response.

Public Sub SaveWorksheet()
On Error GoTo ErrorHandler
Dim wrksht As Worksheet
For Each wrksht In ActiveWorkbook.Worksheets
wrksht.Activate
Range("A1").Select
Next wrksht
ActiveWorkbook.Worksheets(1).Activate
ActiveWorkbook.Save

SubExit:
Exit Sub

ErrorHandler:
MsgBox "There has been the following error. Please contact the macro
administrator." & _
vbCrLf & Err.Number & " " & Err.Description
GoTo SubExit
End Sub

Sprinks

"Tom Ogilvy" wrote:

Use the before save event for the thisworkbook object/module

http://www.cpearson.com/excel/events.htm

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sh as Worksheet
ThisWorkbook.Activate
for each sh in thisworkbook.Worksheets
sh.Activate
sh.Range("A1").Select
Next
thisworkbook.worksheets(1).Select
Cancel = True
On Error goto ErrHandler
Application.EnableEvents = False
ThisWorkbook.Save
ErrHandler:
Application.EnableEvents = True
End Sub

This must be in the ThisWorkbook Module.

--
Regards,
Tom Ogilvy

"Sprinks" wrote in message
...
I'd like a macro that, prior to saving the current workbook, would
position
the cursor in cell A1, and make the first sheet the active one. The
following code does not work. Can anyone help?

Public Sub SaveWorksheet()
Dim wrksht As Worksheet
For Each wrksht In ActiveWorkbook.Worksheets
Range("A1").Select
Next wrksht
Worksheets(1).Select
ActiveWorkbook.Save
End Sub

Sprinks

Save Macro
 

Thanks for your response, Bob, but this code will not work because the
Activate method is required to select each sheet.

Sprinks

"Bob Phillips" wrote:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wrksht As Worksheet
For Each wrksht In ActiveWorkbook.Worksheets
wrksht.Range("A1").Select
Next wrksht
Worksheets(1).Select
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sprinks" wrote in message
...
I'd like a macro that, prior to saving the current workbook, would
position
the cursor in cell A1, and make the first sheet the active one. The
following code does not work. Can anyone help?

Public Sub SaveWorksheet()
Dim wrksht As Worksheet
For Each wrksht In ActiveWorkbook.Worksheets
Range("A1").Select
Next wrksht
Worksheets(1).Select
ActiveWorkbook.Save
End Sub

Sprinks