Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Macro
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Macro
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Macro
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Macro | Excel Discussion (Misc queries) | |||
conditional sum macro | Excel Programming | |||
[B]Conditional Macro?[/B] | Excel Discussion (Misc queries) | |||
conditional sum and macro | Excel Discussion (Misc queries) | |||
Conditional Macro | Excel Worksheet Functions |