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).


Code:
--------------------

Public Sub UpdateWorksheets()

Dim Wks

For Each Wks In Worksheets
Wks.Range("E28") = Wks.Range("G28")
Next Wks

End Sub

 

[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

 

[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

 

[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

Davi

 

[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>.)

 

[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.