How to get a macro to work on another worksheet

[[email protected]]

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]

[email protected] 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

[Rowan]

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

"[email protected]" 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.
>
>

[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


--
HTH

Bob Phillips

"Rowan" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "[email protected]" 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.
> >
> >

[[email protected]]

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.

[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

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

--
HTH

Bob Phillips

<[email protected]> wrote in message
news:[email protected]...
> 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.
>