How to get a macro to work on another worksheet

[RJQMAN]

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.

 

[Doug]

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

 

[Guest]

You don't have to select the cells to perform an operation on them. Try it
like this:

Sub Hide()
With Sheets("Sheet2")
.Rows("2:15").Hidden = False
.Rows("10:12").Hidden = True
End With
With Sheets("Sheet3")
.Rows("2:15").Hidden = False
.Rows("10:12").Hidden = True
End With
End Sub

Hope this helps
Rowan

 

[Bob Phillips]

If it is the same action create another procedure

Sub Hide()
HideRows Worksheets("Sheet2")
HideRows Worksheets("Sheet3")
End Sub

Private Sub HideRows(sh As Worksheet)
With sh
.Rows("2:15").Hidden = False
.Rows("10:12").Hidden = True
End With
End Sub

 

[RJQMAN]

Everything you have suggested works! I do not yet understand why mine
did not, but thank you for solving my problem. I am an old dude that
used to program in Fortran - it seemed easy compared to trying to learn
this. I guess it is partially true - it is hard to teach an old dog
new tricks.

I have another question, which I should probably post as a different
one. Can I have a macro Unprotect a protected sheet and then
re-protect it after the changes have been made? I will repost if I do
not get a response.

Many thanks for your help. I know I will have more questions, and the
help on this newsgroup is extraordinary.

 

[Bob Phillips]

Simple answer is yes. A standard approach to protected worksheets.

Activesheet.Unprotect
'do your stuff
Activesheet.Protect

 

[Piranha]

RJQMAN,

Put something like this for active sheet at the top of your code:
ActiveSheet.unprotect

Put this at the bottom:
ActiveSheet.protect

If you want a password usesomething like:
ActiveSheet.Unprotect Password:="mypassword"

and:
ActiveSheet.Protect Password:="mypassword"

Something like this will specify certain sheets:
ThisWorkbook.Worksheets("Sheet1).Unprotect("mypassword")
ThisWorkbook.Worksheets("Sheet1).Protect("mypassword")

Dave