Running Macros Across Worksheets

  • Thread starter Thread starter Randy Numbers
  • Start date Start date
R

Randy Numbers

Greetings,

My app contains 3 tabs, with a "superset" of rows on each. Depending upon
the view selected in cell B2 in TAB 1, I want to display different sets of
rows for each sheet. I have the logic that allows me to run a macro on each
tab that shows the right rows. Now what I want to do is have a single marco
that will set the rows on all three tabs at once.

Here's what I have:


Sub SHOWVIEWS()
SmartRefresh2 ("Background")
SmartRefresh2 ("IT Environ")
SmartRefresh2 ("Adv Options")
End Sub

_______

Sub SmartRefresh2(WSHEET)
....

Application.ScreenUpdating = False
With Worksheets(WSHEET)
.Unprotect Password:=Worksheets("background").Range("a1").Value
Cells.Select
UnhideAllRows2 (WSHEET)
.....

_______

Sub UnhideAllRows2(WSHEET2)

....
With Worksheets(WSHEET2)
.Unprotect Password:=Worksheets("background").Range("a1").Value

Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
.....
___

Everything works as expected for Tab 1 when I make the change in cell B2;
however, I get an error when TAB 2 trys to get processed. Specifically, the
last two lines of the last macro ( Selection.EntireRow.Hidden = False)
returns an error "Unable to set the hidden property..."

I've checked, and the sheet is unlocked. Many, many thanks../Randy
 
You have unqualified ranges in your code:

Cells.Select

Refers to the activesheet (when it's in a General module). Even though you
stuck a "with worksheets(...)" before it, you didn't tell it to use the Cells on
that worksheet.

And another problem is that you can't select cells on a sheet that isn't active.

So you could rewrite parts of your code:

Sub UnhideAllRows2(WSHEET2)
With Worksheets(WSHEET2)
.Unprotect Password:=Worksheets("background").Range("a1").Value
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
end with
end sub

to something like this:

Sub UnhideAllRows2(WSHEET2)
With Worksheets(WSHEET2)
.select 'select the worksheet
.Unprotect Password:=Worksheets("background").Range("a1").Value
.Cells.Select 'note the leading dot.
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
end with
end sub

(I added a .select and a dot in front of Cells)
With that leading dot, it means to refer to the object in the previous "with"
statement.

But it's not usually required to do any selecting at all. You can just act on
the object (sheet/range) directly:

Sub UnhideAllRows2(WSHEET2)
With Worksheets(WSHEET2)
.Unprotect Password:=Worksheets("background").Range("a1").Value
.cells.EntireRow.Hidden = False
.cells.EntireColumn.Hidden = False
end with
end sub

It means that your code will execute faster (may not be noticeable in short
routines), but it really makes it easier to review/update the code--no matter
what the length of code.
 
Thanks for posting back.

I didn't see the other thread (just at the bottom of the viewable screen and I
didn't scroll down).

But it's nice to see that you read it.
 
Back
Top