Changing one range on multiple sheets.

M

Mike-hime

There is a point in my code where I would like to reset the same range on
several worksheets to empty. Is there a way to do this without writing a
line of code for each sheet?

It looks like this right now:

MySheet1.Range("C3") = ""
MySheet2.Range("C3") = ""
MySheet2.Range("C3") = ""
etc...

TIA

Mike-hime
 
D

David McRitchie

Hi Mike,
Since you are in programming and you have to process each
sheet separately in VBA unlike grouped sheets outside of programming.

If the sheet are grouped you can process the grouped worksheets
with programming without knowing the names of the sheets.
the insert rows example in
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
where Gary L. Brown, included a revision to my macro to process
Grouped sheets
 
D

Doug Glancy

Mike,

You could do something like this:

Sub test()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets(Array("MySheet1", "MySheet2",
"MySheet3"))
ws.Range("C3") = ""
Next ws

End Sub

hth,

Doug
 
M

Mike-hime

If anyone was curious, a friend of mine found this method, using an array
and the FillAcrossSheets function:

MySheet1.Range("C3") = ""
MySheets = Array("Sheet1", "Sheet2", Sheet3",etc...)
Sheets(MySheets).FillAcrossSheets Range:=MySheet1.Range("C3")

This copies range C3 on the specified sheet and pastes it into the same
range on all the sheets in the array. It's handy! And no grouping involved
:)
But thx for the help anyway David, it's always appreciated.

-Mike-hime



David McRitchie said:
Hi Mike,
Since you are in programming and you have to process each
sheet separately in VBA unlike grouped sheets outside of programming.

If the sheet are grouped you can process the grouped worksheets
with programming without knowing the names of the sheets.
the insert rows example in
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
where Gary L. Brown, included a revision to my macro to process
Grouped sheets

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

There is a point in my code where I would like to reset the same range on
several worksheets to empty. Is there a way to do this without writing a
line of code for each sheet?

It looks like this right now:

MySheet1.Range("C3") = ""
MySheet2.Range("C3") = ""
MySheet2.Range("C3") = ""
etc...

TIA

Mike-hime
Click to expand...
Click to expand...
 
Top