Solved: BeforeSave Event

[zoom38]

Im trying to have vba code automatically change the active cell to cell a1 on all of the worksheets in the workbook beforesave event. This is so when the file is opened the next time all of the sheets will be set to cell a1. The following code is what I came up with but it only works on the activesheet.
[VBA]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wkSheet As Worksheet

For Each wkSheet In Worksheets
Range("a1").Select
Next wkSheet
End Sub

[/VBA]
Any suggestions?
Thanks
Gary

[Jacob Hilderbrand]

Each worksheet needs to be active when you want to select a range. i.e.

[vba]
Thisworkbook.Activate
For Each wkSheet In Worksheets
wkSheet.Activate
Range("A1").Select
Next
[/vba]

[lenze]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sh As Worksheet
    For Each sh In Worksheets
        sh.Activate
        Range("a1").Select
    Next sh
    Sheet1.Select
End Sub

This seems to work

lenze

[zoom38]

Thanks DRJ you fixed my code and solved it.
Gary

[Jacob Hilderbrand]

You're Welcome

Take Care

[zoom38]

Thanks for the replies. I came across another issue with this. No matter what sheet you are on, when you hit save it brings you to the last sheet. If you are on the 5th sheet of 18, how do you have it automatically bring you back to the 5th sheet when you hit save since it no longer is the active sheet?

[Bob Phillips]

Untested, but should work

[vba]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim thisSheet As Worksheet
Dim sh As Worksheet
Set thisSheet = ActiveSheet
For Each sh In Worksheets
sh.Activate
Range("a1").Select
Next sh
thisSheet.Activate
End Sub
[/vba]

[zoom38]

Thanks xld that does work without a hitch.
Gary