Conditional Macro

[Larry S]

I'd like to create a macro that will print either sheet #1, #2, #3, #4 or #5
based on the value in a particular cell.

Thanks, Larry

 

[Don Guillett]

Details?

 

[Per Jessen]

Hi

Maybe this will do what you want (untested).

Copy the macro into the code sheet for ThisWorkbook, as it's an event code.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Select Case workshees("Sheet1").Range("A1").Value
Case Is = 1
Worksheets("Sheet1").PrintOut
Case Is = 2
Worksheets("Sheet1").PrintOut
...

End Select

Application.EnableEvents = True
End Sub

Best regards,
Per

 

[Pete Rooney]

Hi, Larry, try this - it assumes that Sheet1 has a cell named "WhatToPrint"
You type a number into this cell and the macro activates the corresponding
worksheet, prints the print area and returns to Sheet1 - you can modify it to
meet your requirements.
The Case Else drops you out of the macro to prevent you from trying to
select and print from a worksheet that doesn't exist, and that isn't catered
for in your Case code.

Cheers

Pete

Sub PrintFromSheets()

Dim WhatToPrint As Range
Set WhatToPrint = Worksheets("Sheet1").Range("WhatToPrint")

Select Case WhatToPrint
Case 2
Sheets("Sheet2").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet1").Activate
Case 3
Sheets("Sheet3").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet1").Activate
Case Else
Exit Sub
End Select
End Sub

 

[Larry S]

thanks for such a quick response. i kinda messed up in my post and said that
i want to print sheets when in fact what i meant to say was that i want to
print a range of pages in a sheet based on a particular cell value.
therefore, if the value in that cell is 3, i want to print pages 1-3. sorry
for the misinformation. the sheet is a form template and has cell borders in
it which is why i can't just qprint and get whatever information is on the
sheet. if there is only one line of information we get multiple pages printed
just because of the borders.

 

[Pete Rooney]

Hi, Larry,

Howabout:

Sub PrintFromSheets()

Dim WhatToPrint As Range
Set WhatToPrint = Worksheets("Sheet1").Range("WhatToPrint")

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=WhatToPrint,
Copies:=1, Collate:=True

End Sub

Cheers

Pete

 

[Larry S]

Thanks Pete. worked perfectly

Hi, Larry,

Howabout:

Sub PrintFromSheets()

Dim WhatToPrint As Range
Set WhatToPrint = Worksheets("Sheet1").Range("WhatToPrint")

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=WhatToPrint,
Copies:=1, Collate:=True

End Sub

Cheers

Pete