Running Same macro in 250 Worksheets in Same Workbook
 

Hi All:

I have an Excel 2000 spk3 workbook which has about 270 identically
formatted worksheets in it. I have a simple macro that clears some
cells and I need to run it from worksheet 11 all the way thru worksheet
270.

I need to automate it so I don't have to go to every worksheet, run the
macro and go to the next and run it and so forth.

Any suggestions would be welcome. :confused:


--
halem2
------------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930
View this thread: http://www.excelforum.com/showthread...hreadid=525787

Running Same macro in 250 Worksheets in Same Workbook
 

Sub abc()
For Each sht In ActiveWorkbook.Worksheets
sht.Activate
Range("A1").Value = 999
Next sht
End Sub

Sub cde()
For Each sht In ActiveWorkbook.Worksheets
sht.Range("A1").Value = 999
Next sht
End Sub

First way activates each sheet and second doesn't. Note that the second
explicity references each sheet

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"halem2" wrote in
message ...

Hi All:

I have an Excel 2000 spk3 workbook which has about 270 identically
formatted worksheets in it. I have a simple macro that clears some
cells and I need to run it from worksheet 11 all the way thru worksheet
270.

I need to automate it so I don't have to go to every worksheet, run the
macro and go to the next and run it and so forth.

Any suggestions would be welcome. :confused:


--
halem2
------------------------------------------------------------------------
halem2's Profile:
http://www.excelforum.com/member.php...fo&userid=9930
View this thread: http://www.excelforum.com/showthread...hreadid=525787

Running Same macro in 250 Worksheets in Same Workbook
 

Thanks Ken. this is how I got it to work.

Sub UpdateAll()
Dim sh As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
'
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
UPDATE
Next
Sheets("710").Select
End Sub


thanks a lot.


--
halem2
------------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930
View this thread: http://www.excelforum.com/showthread...hreadid=525787

Running Same macro in 250 Worksheets in Same Workbook
 

You're welcome :-)

Regards
Ken..............

"halem2" wrote in
message ...

Thanks Ken. this is how I got it to work.

Sub UpdateAll()
Dim sh As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
'
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
UPDATE
Next
Sheets("710").Select
End Sub


thanks a lot.


--
halem2
------------------------------------------------------------------------
halem2's Profile:
http://www.excelforum.com/member.php...fo&userid=9930
View this thread: http://www.excelforum.com/showthread...hreadid=525787