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

 

[gocush]

Assuming you have previously selected ranges in each of your sheets, try:


Dim Sh As Worksheet
dim c as Range
For Each Sh In ActiveWindow.SelectedSheets
For each c in Selection
'your code
Next c
Next Sh

 

[Jon Peltier]

If you do not change the active sheet, the selection remains whatever is selected on
the real active sheet. I ran these one-line loops in the debug window, with a two
worksheet workbook.

Without activating each sheet, the selected range is unchanged:

for each ws in worksheets : debug.Print selection.address(external:=true) : next
[Book1]Sheet1!$A$1:$C$3
[Book1]Sheet1!$A$1:$C$3

activating the sheet causes its selected area to become the selection:

for each ws in worksheets : ws.activate : debug.Print
selection.address(external:=true) : next
[Book1]Sheet1!$A$1:$C$3
[Book1]Sheet2!$D$4:$F$6

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