Running Same macro in 250 Worksheets in Same Workbook

[halem2]

Hi All

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

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

Any suggestions would be welcome. :confused

 

[jjanyan]

You need to open up VB inside Excel and edit the macro.

Since I can't see your code, I can't tell you exactly what to do, but
you can use this line of code to reference different sheets in your
workbook,

For x = 11 To 270
Sheets(x).Cells(1, 2).Value = ""
Sheets(x).Cells(3,10).Value = ""
(etc...)
Next x

that would make sheet 1; cell 1,2; blank. Before you do all this, make
sure you back up your workbook.

if for some reason your sheets you want to work with are out of order,
i suppose you could make an array of numbers with like this....
Dim MyArray(270) as Integer
MyArray(1) = 1
MyArray(2) = 0
MyArray(3) = 0
MyArray(4) = 1
etc...
then

For x=1 to 270
if MyArray(x) = 1 then
Sheets(x).Cells(1, 2).Value = ""
Sheets(x).Cells(3,10).Value = ""
end if
next x

This will only clear out the cells on the sheets you've flagged.
Initially it will take alot of time to write all that out, but if you
use this often, it will eventually save you time.

Another thing. I think it's possible to reorder your sheets from inside
VB for Excel. On the left will be a list of all the sheets with their
number. You could also renumber all the sheets you want to apply this
to and put them in order like 30-300.

Hope that helped.
J

 

[Ken Wright]

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]

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.

 

[Beege]

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.

halem,

could you record the macro?

Select sheets 11-250, group them
Clear cells (will clear the same cell on each sheet)
Ungroup

Stop recording macro

Assign it a key

mebbe?

Beege

 

[Ken Wright]

You're welcome

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