Change sheet reference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 said:
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
Click to expand...
different button is pressed. Can this be done without copy the macro over
and over? I have a lot of sheets.
 
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.
 
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.
 
Back
Top