Set all sheets to see A1 as left top cell

[Diddy]

Hi everyone,

I'm using Excel 2003 and would like a macro that sets all sheets back to A1
so that when I save a wkbk I don't have to check each sheet to see that it is
displayed from the top.

I've tried this
Option Explicit

Dim Shts As Worksheet
Sub SettoA1()
'Resets all sheets to A1 (in my dreams!)

For Each Shts In ThisWorkbook.Worksheets
Shts.Activate
Application.Goto Reference:="R1C1"
Next
End Sub

I thought that this should work but does not seem to. What am I doing wrong?

I also thought that maybe it wasn't working because with some of my wkbks,
the data sheets are sent to me from another dept and they use Rows 1-5 for
info about where the data has come from and these rows are then hidden,
sheets are protected and also have data val on them so would be a hassle to
unhide each sheet.
So I took these sheets out to test macro and still did not seem to work.

If someone could tell me how to make it work, could they also consider a
workaround for my sheets with hidden rows please.

Many thanks

 

[paul.robinson]

Hi
Open the ThisWorkbook module in the VB Editor and paste in this

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1").Activate
End Sub

regards
Paul

 

[Peter T]

Sub test()
Dim wb As Workbook
Dim shtOrig As Object
Dim ws As Worksheet

Set wb = ThisWorkbook
wb.Activate
Set shtOrig = ActiveSheet
For Each ws In wb.Worksheets
ws.Activate
Application.Goto ws.Cells.SpecialCells(xlCellTypeVisible)(1), True
Next
shtOrig.Activate

End Sub

Regards,
Peter T

 

[Diddy]

Hi Paul,

sorry for late reply.

The code was great it returned the cursor to a1 each time sheet opened. I'll
definitely use this but I've used Mike H's solution (posted twice in error -
getting Service Unavailable message) for the stuff I'm working on now.

Thnak You!
Diddy

 

[Diddy]

Hi Peter,

Haven't been able to get this to work. It runs ok but does not seem to reset
at A1.

Thanks for your reply

Cheers
Diddy