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

Details?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

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

"Larry S" skrev i meddelelsen
...
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

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" wrote:

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

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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

Thanks Pete. worked perfectly

"Pete Rooney" wrote:

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" wrote:

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" wrote:

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" wrote:

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