Changing one range on multiple sheets.

  • Thread starter Thread starter Mike-hime
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top