loop through all worksheets
in [Programming]
|
From: ordnance1 on 19 May 2010 01:27 My workbook has over 100 worksheets. I want to be able to execute the code below, have it cycle through all worksheets and when done leave the worksheet that was active when the code was started active. Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Activate .Range("A3").Activate End With Next n Application.ScreenUpdating = True
From: ozgrid.com on 19 May 2010 01:42 Sub LoopAllWS() Dim ws As Worksheet Dim wsStart As Worksheet Set wsStart = ActiveSheet For Each ws In Worksheet With ws 'No need to use Select use .With code here End With Next ws wsStart.Activate End Sub -- Regards Dave Hawley www.ozgrid.com "ordnance1" <ordnance1(a)comcast.net> wrote in message news:F7A036D7-7091-435B-8573-96B9B6AF75DE(a)microsoft.com... > My workbook has over 100 worksheets. I want to be able to execute the code > below, have it cycle through all worksheets and when done leave the > worksheet that was active when the code was started active. > > Application.ScreenUpdating = False > Dim n As Single > For n = 1 To Sheets.Count > With Sheets(n) > .Activate > .Range("A3").Activate > End With > Next n > Application.ScreenUpdating = True
From: ordnance1 on 19 May 2010 01:56 Thanks, but I get an Object Required error and the following line of text is highlighted: For Each ws In Worksheet "ozgrid.com" <dave(a)ozgrid.com> wrote in message news:ulvgcYx9KHA.3176(a)TK2MSFTNGP05.phx.gbl... > Sub LoopAllWS() > Dim ws As Worksheet > Dim wsStart As Worksheet > > Set wsStart = ActiveSheet > For Each ws In Worksheet > With ws > 'No need to use Select use .With code here > End With > Next ws > wsStart.Activate > End Sub > > > > > -- > Regards > Dave Hawley > www.ozgrid.com > "ordnance1" <ordnance1(a)comcast.net> wrote in message > news:F7A036D7-7091-435B-8573-96B9B6AF75DE(a)microsoft.com... >> My workbook has over 100 worksheets. I want to be able to execute the >> code below, have it cycle through all worksheets and when done leave the >> worksheet that was active when the code was started active. >> >> Application.ScreenUpdating = False >> Dim n As Single >> For n = 1 To Sheets.Count >> With Sheets(n) >> .Activate >> .Range("A3").Activate >> End With >> Next n >> Application.ScreenUpdating = True >
From: Per Jessen on 19 May 2010 03:06 There is a typo in the statement, use this: For Each ws In Worksheets Regards, Per "ordnance1" <ordnance1(a)comcast.net> skrev i meddelelsen news:#VWy4fx9KHA.4816(a)TK2MSFTNGP02.phx.gbl... > Thanks, but I get an Object Required error and the following line of text > is highlighted: > > For Each ws In Worksheet > > > > "ozgrid.com" <dave(a)ozgrid.com> wrote in message > news:ulvgcYx9KHA.3176(a)TK2MSFTNGP05.phx.gbl... >> Sub LoopAllWS() >> Dim ws As Worksheet >> Dim wsStart As Worksheet >> >> Set wsStart = ActiveSheet >> For Each ws In Worksheet >> With ws >> 'No need to use Select use .With code here >> End With >> Next ws >> wsStart.Activate >> End Sub >> >> >> >> >> -- >> Regards >> Dave Hawley >> www.ozgrid.com >> "ordnance1" <ordnance1(a)comcast.net> wrote in message >> news:F7A036D7-7091-435B-8573-96B9B6AF75DE(a)microsoft.com... >>> My workbook has over 100 worksheets. I want to be able to execute the >>> code below, have it cycle through all worksheets and when done leave the >>> worksheet that was active when the code was started active. >>> >>> Application.ScreenUpdating = False >>> Dim n As Single >>> For n = 1 To Sheets.Count >>> With Sheets(n) >>> .Activate >>> .Range("A3").Activate >>> End With >>> Next n >>> Application.ScreenUpdating = True >>
From: Javed on 19 May 2010 03:22 On May 19, 10:27 am, "ordnance1" <ordnan...(a)comcast.net> wrote: > My workbook has over 100 worksheets. I want to be able to execute the code > below, have it cycle through all worksheets and when done leave the > worksheet that was active when the code was started active. > > Application.ScreenUpdating = False > Dim n As Single > For n = 1 To Sheets.Count > With Sheets(n) > .Activate > .Range("A3").Activate > End With > Next n > Application.ScreenUpdating = True Probably you want to activate the first cell of each sheet so that user get it right A1 on reaching sheets You can use following code for speed Sub YourSubName() Dim ws As Worksheet Dim wsStart As Worksheet Set wsStart = ActiveSheet For Each ws In Worksheet If ws.Visible = xlSheetVisible Then Application.GoTo ws.Range("a1"), True End If Next ws wsStart.Activate End Sub
|
Next
|
Last
Pages: 1 2 Prev: stop VBA in solver when maximum iterations reached Next: convert decimal to sexagesimal |