looping through selected sheet tabs

[Gary Adamson]

I am trying to write a program to go through just the
selected sheets in a workbook and then loop through the
selected cells in each sheet.


Sub Replace2()
Dim c As Object, sht As Worksheet
For Each sht In 'Is there a way to get an array of
'the sheets that are selected.

For Each c In sht.Selection 'I thought this is how
'you get the range of
'selected cells.
Debug.Print c
Next c
Next sht
End Sub

 

[Jon Peltier]

Gary -

For Each sht in ActiveWorkbook.Worksheets

If you are going to work on the selected range, you need to activate each sheet
first. Do you want the same range on every sheet? Then do something like this on the
first sheet:

Dim sRng as string
Dim c as range

If Typename(Selection) = "Range" then
sRng = selection.address
End If

' in the loop...
For each c in sht.Range(sRng)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[peter]

Gary

If you want to "hard code" your sheet selection then you can use

Dim Sh As Worksheet
For Each Sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
'your code here
Next

Alternatively, if you want to use the currently selected sheets then
you can use

Dim Sh As Worksheet
For Each Sh In ActiveWindow.SelectedSheets
'your code here
Next

Peter