Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-setting view of sheets so A1 is top left
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 -- Diddy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-setting view of sheets so A1 is top left
Hi,
Try this Sub Sonic() Dim shts As Worksheet Const TopLeft As String = "A1" For Each Sheet In Worksheets Application.Goto Sheet.Range(TopLeft), scroll:=True Next End Sub Mike "Diddy" wrote: 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 -- Diddy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-setting view of sheets so A1 is top left
Thanks Mike,
Sorry for the delay I seem to be having problems accessing the website. Thanks for your solution!! Cheers Diddy "Mike H" wrote: Hi, Try this Sub Sonic() Dim shts As Worksheet Const TopLeft As String = "A1" For Each Sheet In Worksheets Application.Goto Sheet.Range(TopLeft), scroll:=True Next End Sub Mike "Diddy" wrote: 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 -- Diddy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to change view in excel to left to right | Excel Discussion (Misc queries) | |||
My Excel view is Right to Left instead of Left to Right !!! | Excel Discussion (Misc queries) | |||
Setting Top and Left of Modal UserForm | Excel Programming | |||
setting the Left or Right HeaderPicture | Excel Programming | |||
View only items in the left column that have X's in right columns | Excel Discussion (Misc queries) |