Cycle through all worksheets
 

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)

Cycle through all worksheets
 

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.

Cycle through all worksheets
 

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

--------------------


Happy Holidays,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=495959

Cycle through all worksheets
 

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" wrote in message
...
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.

Cycle through all worksheets
 

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


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=495959

Cycle through all worksheets
 

NO need to activate

--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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" wrote in message
...
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.

Cycle through all worksheets
 

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

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=49595

Cycle through all worksheets
 

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

Cycle through all worksheets
 

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

"davidm" wrote in
message ...

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