Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get a macro to work on another worksheet
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link one worksheet in a workbook to another worksheet in same work | Excel Discussion (Misc queries) | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
How do I make a macro work in one worksheet only | Excel Programming | |||
Get Macro on one Worksheet to Work on Others | Excel Programming | |||
having a macro work only on a worksheet | Excel Programming |