How to get a macro to work on another worksheet
 

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.

How to get a macro to work on another worksheet
 

How to get a macro to work on another worksheet
 

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

" 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.

How to get a macro to work on another worksheet
 

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" wrote in message
...
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

" 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.

How to get a macro to work on another worksheet
 

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.

How to get a macro to work on another worksheet
 

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

Activesheet.Unprotect
'do your stuff
Activesheet.Protect

--
HTH

Bob Phillips

wrote in message
ups.com...
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.

How to get a macro to work on another worksheet
 

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("mypass word")
ThisWorkbook.Worksheets("Sheet1).Protect("mypasswo rd")

Dave
Wrote:
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
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435
View this thread: http://www.excelforum.com/showthread...hreadid=388589