Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set all sheets to see A1 as left top cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set all sheets to see A1 as left top cell
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 On Mar 19, 11:22*am, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set all sheets to see A1 as left top cell
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set all sheets to see A1 as left top cell
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 " wrote: 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 On Mar 19, 11:22 am, 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set all sheets to see A1 as left top cell
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 "Peter T" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I unhide all sheets on the left of Excel | Excel Programming | |||
can i put sheets on the left and right of my screen | Excel Discussion (Misc queries) | |||
Delete Sheets to the left | Excel Programming | |||
Adding new sheets to the right instead of the default left | Excel Programming | |||
How to make a cell appear in upper left (top left) corner of works | Excel Programming |