ExcelBanter - View Single Post - How to get a macro to work on another worksheet

Doug[_14_]

wrote:
I am just learning to write macros, and I am trying to write a macro to
work on several sheets in the same workbook. My goal is to trigger the
macro from a button on sheet one, then have the macro display rows 2
through 15 on sheet 2 if they are hidden, and then to hide rows 10-12
on sheet 2, and then do the same thing on sheet3. Here is the way it
now stands;

Sub Macro1()
'
' Macro1 Macro
'
With Sheets("Sheet2")
Range("A2:A15").Select
Selection.EntireRow.Hidden = False
Range("A10:A12").Select
Selection.EntireRow.Hidden = True
End With
With Sheets("Sheet3")
Range("A2:A15").Select
Selection.EntireRow.Hidden = False
Range("A10:A12").Select
Selection.EntireRow.Hidden = True
End With
End Sub

The button works great at triggering the macro. However, the result of
the macro is that it displays rows 2-15 and then hides rows 10-12 (As I
wanted it to do), but it affects sheet 1 only. I did not even want it
to affect sheet 1. It seems to have no affect on the other two sheets.
How can I make it function on other sheets instead of sheet 1? What
have I done wrong? (I am working in Excel 2000 by the way).

Thanks in advance for any help you can provide.

Bob Q.

Simplify your macro and try it this way. You don't need to move to or
"select" the range.

Sub Macro1()
Sheets("Sheet2").Range("A2:A15").EntireRow.Hidden = False
Sheets("Sheet2").Range("A10:A12").EntireRow.Hidden = True
Sheets("Sheet3").Range("A2:A15").EntireRow.Hidden = False
Sheets("Sheet3").Range("A10:A12").EntireRow.Hidden = True
End Sub

Doug