Cycle through all worksheets

[Patrick Simonds]

I have a workbook with 98 worksheets, What I need is a routine which will
cycle through 98 worksheets and when done leave me where I started (which is
not necessarily the first worksheet)

[Patrick Simonds]

Origanally I was using:

Private Sub Worksheet_Calculate()
Me.Range("E28").Value = Me.Range("G28")
End Sub

But there apparently got to be so many calculations that it would take the
code for ever to finish running, so I swithche to

Private Sub Worksheet_Activate()
Me.Range("E28").Value = Me.Range("G28")
End Sub

To achieve the samething. If someone can point me to better way I would be
very appricative.

[Leith Ross]

Hello Patrick,

Add a VBA module to your project and copy this code into it. You can run the macro by selecting it the macro list (Alt + F8).

Please Login or Register  to view this content.

Happy Holidays,
Leith Ross

[Gary Keramidas]

maybe something like this

Option Explicit
Sub doAll()

Dim wks As String
Dim sh As Worksheet
wks = ActiveSheet.Name
For Each sh In ThisWorkbook.Sheets
sh.Activate
'do something
Next
Worksheets(wks).Activate
End Sub

--


Gary


"Patrick Simonds" <[email protected]> wrote in message
news:[email protected]...
> Origanally I was using:
>
> Private Sub Worksheet_Calculate()
> Me.Range("E28").Value = Me.Range("G28")
> End Sub
>
> But there apparently got to be so many calculations that it would take the
> code for ever to finish running, so I swithche to
>
> Private Sub Worksheet_Activate()
> Me.Range("E28").Value = Me.Range("G28")
> End Sub
>
> To achieve the samething. If someone can point me to better way I would be
> very appricative.
>
>

[davidm]

Patrick,

In a general module,


Sub CycleThroWkshts()
Dim sht as Worksheet

Set acSheet=ActiveSheet

For each sht in WorkSheets
sht.Activate
Range("E28").Value = Range("G28")
Next

acSheet.activate

End sub

[Don Guillett]

NO need to activate

--
Don Guillett
SalesAid Software
[email protected]
"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:[email protected]...
> maybe something like this
>
> Option Explicit
> Sub doAll()
>
> Dim wks As String
> Dim sh As Worksheet
> wks = ActiveSheet.Name
> For Each sh In ThisWorkbook.Sheets
> sh.Activate
> 'do something
> Next
> Worksheets(wks).Activate
> End Sub
>
> --
>
>
> Gary
>
>
> "Patrick Simonds" <[email protected]> wrote in message
> news:[email protected]...
>> Origanally I was using:
>>
>> Private Sub Worksheet_Calculate()
>> Me.Range("E28").Value = Me.Range("G28")
>> End Sub
>>
>> But there apparently got to be so many calculations that it would take
>> the code for ever to finish running, so I swithche to
>>
>> Private Sub Worksheet_Activate()
>> Me.Range("E28").Value = Me.Range("G28")
>> End Sub
>>
>> To achieve the samething. If someone can point me to better way I would
>> be very appricative.
>>
>>
>
>

[davidm]

Don,

There is need to activate if the respective sheets are not referenced. It's all a matter of style to use

For each sht in Worksheets
sht.Range("E28").Value = sht.Range("G28")
Next

or


For each sht in Worksheets
sht.activate
Range("E28").Value = Range("G28")
Next

With screenupdating turned off, time efficiency is about equalized between the two.

David

[Dave Peterson]

Except that activating sheets means that you may want to keep track of where you
started--to go back when you're done.

And if that code is behind a worksheet, then those unqualified ranges will still
refer to the sheet that owns the code--not the activesheet. (Yes, I saw your
warning about putting the code in a general module.)

(I think it's more than a matter of style--but I don't have a better word for it
<vbg>.)

davidm wrote:
>
> Don,
>
> There is need to activate if the respective sheets are not referenced.
> It's all a matter of style to use
>
> For each sht in Worksheets
> sht.Range("E28").Value = sht.Range("G28")
> Next
>
> or
>
> For each sht in Worksheets
> sht.activate
> Range("E28").Value = Range("G28")
> Next
>
> David
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=495959

--

Dave Peterson

[Don Guillett]

It IS widely accepted coding practice that selections/activations should be
avoided where ever possible. It is not merely a matter of style as it slows
down the code, etc.

--
Don Guillett
SalesAid Software
[email protected]
"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> Don,
>
> There is need to activate if the respective sheets are not referenced.
> It's all a matter of style to use
>
> For each sht in Worksheets
> sht.Range("E28").Value = sht.Range("G28")
> Next
>
> or
>
>
> For each sht in Worksheets
> sht.activate
> Range("E28").Value = Range("G28")
> Next
>
> David
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
> http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=495959
>