Move to a specific cell when closing worksheet

[Guest]

Hi,
Hope this is an easy one for someone.
As a novice with VBA I could do with some help in adding code to a worksheet
that will automatically move the active cell back to being cell A1 before I
close my worksheet.
Any suggestions?
Thanks,
Dickie

 

[NickHK]

Dickie,
You cannot close a worksheet, only a workbook. Put either of these in the
ThisWorkbook module:

If you mean for all worksheets:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
WS.Select
WS.Range("A1").Activate
Next
'Return to the first WS if desired
ThisWorkbook.Worksheets(1).Select
End Sub

Or for only a specific WS:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook.Worksheets(1)
.Select
.Range("A1").Activate
End With
End Sub

NickHK

 

[Guest]

Dickie :

Paste this into the code for the worksheet.

(do that by right clicking there sheet tabe at the bottom and select view
code, then select thisworkbook and paste it in there).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wn As Worksheet
Dim w As Worksheet
Set wn = ActiveSheet ' get current worksheet
For Each w In Worksheets
w.Select
Range("A1").Select
Next w
wn.Activate ' set current worksheet
' or sheets(1).activcat if want first sheet
End Sub