Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change sheet reference
Hello all,
I am new to this level of macros. I have the following: Sub CritSuccessFY04() ' ' PrintFY04AccntAcq Macro ' Macro recorded 12/3/2003 by cwilson ' ' Sheets("Monthly Summary").Select Range("E215:P233").Select ActiveSheet.PageSetup.PrintArea = "$E$215:$P$233" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ActiveSheet.PageSetup.PrintArea = "" Sheets("Print Macros").Select End Sub I have a least 5 macros and I want them to run for different sheets if a different button is pressed. Can this be done without copy the macro over and over? I have a lot of sheets. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change sheet reference
What kind of button - From the Forms toolbar or a commandbutton from the
Control Toolbox toolbar? You want to make your five macros 1 macro? -- Regards, Tom Ogilvy "Chris" wrote in message ... Hello all, I am new to this level of macros. I have the following: Sub CritSuccessFY04() ' ' PrintFY04AccntAcq Macro ' Macro recorded 12/3/2003 by cwilson ' ' Sheets("Monthly Summary").Select Range("E215:P233").Select ActiveSheet.PageSetup.PrintArea = "$E$215:$P$233" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ActiveSheet.PageSetup.PrintArea = "" Sheets("Print Macros").Select End Sub I have a least 5 macros and I want them to run for different sheets if a different button is pressed. Can this be done without copy the macro over and over? I have a lot of sheets. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change sheet reference
I am using buttons created Forms toolbar. All of my worksheets are set up the same and I am creating a print page so that management can just click the buttons and print what they need. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change sheet reference
Sub CritSuccessFY04()
' ' PrintFY04AccntAcq Macro ' Macro recorded 12/3/2003 by cwilson ' ' Dim oBtn as Button sName = Application.Caller set oBtn = Activesheet.Buttons(sName) Select Case sName Case "Button1" sSh = "Sheet1" Case "Button2" sSh = "Sheet2" Case "Button3" sSh = "Sheet3" ' ... etc End Select Sheets(sSh).Select Range("E215:P233").Select ActiveSheet.PageSetup.PrintArea = "$E$215:$P$233" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ActiveSheet.PageSetup.PrintArea = "" Sheets("Print Macros").Select End Sub I don't know where your buttons are or how you intend to differentiate between the buttons - perhaps each has a caption that contains the sheet name or you would use the caption in the case statement. Anyway, assign this macro to all of your buttons. This should give you some idea on how to do what you want. -- Regards, Tom Ogilvy "Chris Wilson" wrote in message ... I am using buttons created Forms toolbar. All of my worksheets are set up the same and I am creating a print page so that management can just click the buttons and print what they need. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change a reference when the sheet change the folder? | Excel Worksheet Functions | |||
Is it possible to change a Sheet reference in a formula? | Excel Discussion (Misc queries) | |||
Change sheet reference in new workbook | Excel Discussion (Misc queries) | |||
Change workbook sheet reference using cell A1 to change a vairable | Excel Worksheet Functions | |||
Change sheet reference in formula? | Excel Programming |