Move to a specific cell when closing worksheet
 

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

Move to a specific cell when closing worksheet
 

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

"Dickie Worton" wrote in message
...
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

Move to a specific cell when closing worksheet
 

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Dickie Worton" wrote:

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