Selecting sheets - II

D

Dr.Schwartz

Yesterday Bob Phillips kindly supplied me with code to select multible sheets.

Now I need to run some code on each of the selected sheets - but how? This
is what I have come up with so far (but is not working):

Dim WSS As Worksheet
For Each WSS In Application.Selection
'my code
Next

Suggestions? Thanks / The Doctor
 
D

Dave Peterson

dim wss as worksheet
For each wss in activewindow.selectedsheets
'your code
next wss

If they've already been selected.
 
D

Dr.Schwartz

I was to fast ticking this one off as OK.

The problem is even though I select more than one sheet the code only takes
effect on the highlighted of the selected sheets. But the code does run as
many times as there are sheets selected.

How can I make it take effect on all selected sheets?

The Doctor
 
D

Dave Peterson

My bet is your code refers to the active sheet.

One way is to replace the activesheet reference with wss.

Another way is to just activate that sheet.

dim wss as worksheet
for each wss in activewindows.selectedsheets
with wss
.range("a1").value = "hi there!"
end with
next wss

or

dim wss as worksheet
for each wss in activewindows.selectedsheets
wss.activate
activesheet.range("a1").value = "hi there!"
next wss

Personally, I like the first option.

If you have trouble, you're gonna have to post more of your code.
 
D

Dr.Schwartz

Hi Dave

I guess you won this bet. I would also prefer the first solution, but since
my code already contains several With statements I will go with the second
solution.

Thank you for your patience / The Doctor
 
Top