Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
Is there a way to set a conditional print area? I have a spread sheet that
has several sections and want to print based on a formula. If anyone can help thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
Use VBA (alt F11)
if -------- then ActiveSheet.PageSetup.PrintArea = ------- end if "PhilosophersSage" wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
put it in as a before print command
Private Sub Workbook_BeforePrint(Cancel As Boolean) if activesheet.range ("A1").value = 2 then ActiveSheet.PageSetup.PrintArea = range ("a1:d10") end if End Sub "Atishoo" wrote: Use VBA (alt F11) if -------- then ActiveSheet.PageSetup.PrintArea = ------- end if "PhilosophersSage" wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
Hi,
You should be able to do something like this. right click yoir sheet tab, view code and paste the code below in. Edit for your ranges and cell to monitor. Private Sub Worksheet_Calculate() On Error Resume Next Select Case Range("J1").Value Case Is = 1 ActiveSheet.PageSetup.PrintArea = "$B$1:$E$20" Case Is = 2 ActiveSheet.PageSetup.PrintArea = "$F$1:$I$20" Case Else End Select End Sub Mike "PhilosophersSage" wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
Sorry forgot to mention I cannot use macros due to company security settings.
"Atishoo" wrote: Use VBA (alt F11) if -------- then ActiveSheet.PageSetup.PrintArea = ------- end if "PhilosophersSage" wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
Sorry forgot to mention I cannot use macros due to company security settings.
"Mike H" wrote: Hi, You should be able to do something like this. right click yoir sheet tab, view code and paste the code below in. Edit for your ranges and cell to monitor. Private Sub Worksheet_Calculate() On Error Resume Next Select Case Range("J1").Value Case Is = 1 ActiveSheet.PageSetup.PrintArea = "$B$1:$E$20" Case Is = 2 ActiveSheet.PageSetup.PrintArea = "$F$1:$I$20" Case Else End Select End Sub Mike "PhilosophersSage" wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
Using this same macro, how would you setup the other attributes for print
settings? Margins Orientation Size Scaling Quality Header Footer Rows to repeat Columns to repeat Gridlines Thank You Learning VBA "Mike H" wrote in message ... Hi, You should be able to do something like this. right click yoir sheet tab, view code and paste the code below in. Edit for your ranges and cell to monitor. Private Sub Worksheet_Calculate() On Error Resume Next Select Case Range("J1").Value Case Is = 1 ActiveSheet.PageSetup.PrintArea = "$B$1:$E$20" Case Is = 2 ActiveSheet.PageSetup.PrintArea = "$F$1:$I$20" Case Else End Select End Sub Mike "PhilosophersSage" wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
You may be able to use a defined name if you can come up with a formula that
returns the addresses that you want to use. My test sheet is named Sheet1. I want to print columns A:B if A1=1. If A1<1, then print columns C:D. This is what I did: Insert|Name|define (xl2003 menus) Names in workbook: Sheet1!Print_Area Refers to: =IF(Sheet1!$A$1=1,Sheet1!$A:$B,Sheet1!$C:$D) (You may have to surround your sheet name with apostrophes: 'Sheet 99'!Print_Area and =IF('Sheet 99'!$A$1=1,'Sheet 99'!$A:$B,'Sheet 99'!$C:$D) If you go into file|page setup, you may find that the print range is changed to a specific range. And you'll have to reapply the Print_Area name. PhilosophersSage wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
That sounds like is should work, but I have played around with what I need
and it does not seem to work. For my first project I need to print only rows that have data in Column B:F so I tried a few verations of: =COUNTA('Continuity Sheet'!$B1:'Continuity Sheet'!$F1)0 However it wants to print all pages, and I understand why as that statment is True. How would I formulate the function to check for data and extend print area if there is data. The main reason for this is this sheet has a formula in A and C:D are merged except row 1:6 "Dave Peterson" wrote: You may be able to use a defined name if you can come up with a formula that returns the addresses that you want to use. My test sheet is named Sheet1. I want to print columns A:B if A1=1. If A1<1, then print columns C:D. This is what I did: Insert|Name|define (xl2003 menus) Names in workbook: Sheet1!Print_Area Refers to: =IF(Sheet1!$A$1=1,Sheet1!$A:$B,Sheet1!$C:$D) (You may have to surround your sheet name with apostrophes: 'Sheet 99'!Print_Area and =IF('Sheet 99'!$A$1=1,'Sheet 99'!$A:$B,'Sheet 99'!$C:$D) If you go into file|page setup, you may find that the print range is changed to a specific range. And you'll have to reapply the Print_Area name. PhilosophersSage wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
First, =counta() counts the number of cells with something in them--including
formulas that evaluate to "". I'm not sure what you were doing with the 0 stuff. =counta('continuity sheet'!$b1:$f1) would be the way I'd write that expression. But I don't think that's what you want. If you're looking to print columns B:F based on the data in column B, then you could use a name that refers to: =OFFSET('Continuity Sheet'!$B$1,0,0,COUNTA('Continuity Sheet'!$B:$B),5) Still using the name: 'Continuity Sheet'!Print_Area (You can't have any empty cells in column B.) Debra Dalgleish explains dynamic range names he http://contextures.com/xlNames01.html#Dynamic PhilosophersSage wrote: That sounds like is should work, but I have played around with what I need and it does not seem to work. For my first project I need to print only rows that have data in Column B:F so I tried a few verations of: =COUNTA('Continuity Sheet'!$B1:'Continuity Sheet'!$F1)0 However it wants to print all pages, and I understand why as that statment is True. How would I formulate the function to check for data and extend print area if there is data. The main reason for this is this sheet has a formula in A and C:D are merged except row 1:6 "Dave Peterson" wrote: You may be able to use a defined name if you can come up with a formula that returns the addresses that you want to use. My test sheet is named Sheet1. I want to print columns A:B if A1=1. If A1<1, then print columns C:D. This is what I did: Insert|Name|define (xl2003 menus) Names in workbook: Sheet1!Print_Area Refers to: =IF(Sheet1!$A$1=1,Sheet1!$A:$B,Sheet1!$C:$D) (You may have to surround your sheet name with apostrophes: 'Sheet 99'!Print_Area and =IF('Sheet 99'!$A$1=1,'Sheet 99'!$A:$B,'Sheet 99'!$C:$D) If you go into file|page setup, you may find that the print range is changed to a specific range. And you'll have to reapply the Print_Area name. PhilosophersSage wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
Dave, Thank you very much!
Just needed a slight modification to your formula suggestion and used =OFFSET('Continuity Sheet'!$A$1,0,0,(COUNTA('Continuity Sheet'!$B:$B))+3,5) works great! "Dave Peterson" wrote: First, =counta() counts the number of cells with something in them--including formulas that evaluate to "". I'm not sure what you were doing with the 0 stuff. =counta('continuity sheet'!$b1:$f1) would be the way I'd write that expression. But I don't think that's what you want. If you're looking to print columns B:F based on the data in column B, then you could use a name that refers to: =OFFSET('Continuity Sheet'!$B$1,0,0,COUNTA('Continuity Sheet'!$B:$B),5) Still using the name: 'Continuity Sheet'!Print_Area (You can't have any empty cells in column B.) Debra Dalgleish explains dynamic range names he http://contextures.com/xlNames01.html#Dynamic PhilosophersSage wrote: That sounds like is should work, but I have played around with what I need and it does not seem to work. For my first project I need to print only rows that have data in Column B:F so I tried a few verations of: =COUNTA('Continuity Sheet'!$B1:'Continuity Sheet'!$F1)0 However it wants to print all pages, and I understand why as that statment is True. How would I formulate the function to check for data and extend print area if there is data. The main reason for this is this sheet has a formula in A and C:D are merged except row 1:6 "Dave Peterson" wrote: You may be able to use a defined name if you can come up with a formula that returns the addresses that you want to use. My test sheet is named Sheet1. I want to print columns A:B if A1=1. If A1<1, then print columns C:D. This is what I did: Insert|Name|define (xl2003 menus) Names in workbook: Sheet1!Print_Area Refers to: =IF(Sheet1!$A$1=1,Sheet1!$A:$B,Sheet1!$C:$D) (You may have to surround your sheet name with apostrophes: 'Sheet 99'!Print_Area and =IF('Sheet 99'!$A$1=1,'Sheet 99'!$A:$B,'Sheet 99'!$C:$D) If you go into file|page setup, you may find that the print range is changed to a specific range. And you'll have to reapply the Print_Area name. PhilosophersSage wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Print Area
If you're adding 3 to the number of rows, that usually means you have empty
cells in that column. I like to fill those empty cells with a formula that still keeps the cell looking empty: ="" Then =counta() will include it and my formula won't need to change when I put some (visible) text into that cell. PhilosophersSage wrote: Dave, Thank you very much! Just needed a slight modification to your formula suggestion and used =OFFSET('Continuity Sheet'!$A$1,0,0,(COUNTA('Continuity Sheet'!$B:$B))+3,5) works great! "Dave Peterson" wrote: First, =counta() counts the number of cells with something in them--including formulas that evaluate to "". I'm not sure what you were doing with the 0 stuff. =counta('continuity sheet'!$b1:$f1) would be the way I'd write that expression. But I don't think that's what you want. If you're looking to print columns B:F based on the data in column B, then you could use a name that refers to: =OFFSET('Continuity Sheet'!$B$1,0,0,COUNTA('Continuity Sheet'!$B:$B),5) Still using the name: 'Continuity Sheet'!Print_Area (You can't have any empty cells in column B.) Debra Dalgleish explains dynamic range names he http://contextures.com/xlNames01.html#Dynamic PhilosophersSage wrote: That sounds like is should work, but I have played around with what I need and it does not seem to work. For my first project I need to print only rows that have data in Column B:F so I tried a few verations of: =COUNTA('Continuity Sheet'!$B1:'Continuity Sheet'!$F1)0 However it wants to print all pages, and I understand why as that statment is True. How would I formulate the function to check for data and extend print area if there is data. The main reason for this is this sheet has a formula in A and C:D are merged except row 1:6 "Dave Peterson" wrote: You may be able to use a defined name if you can come up with a formula that returns the addresses that you want to use. My test sheet is named Sheet1. I want to print columns A:B if A1=1. If A1<1, then print columns C:D. This is what I did: Insert|Name|define (xl2003 menus) Names in workbook: Sheet1!Print_Area Refers to: =IF(Sheet1!$A$1=1,Sheet1!$A:$B,Sheet1!$C:$D) (You may have to surround your sheet name with apostrophes: 'Sheet 99'!Print_Area and =IF('Sheet 99'!$A$1=1,'Sheet 99'!$A:$B,'Sheet 99'!$C:$D) If you go into file|page setup, you may find that the print range is changed to a specific range. And you'll have to reapply the Print_Area name. PhilosophersSage wrote: Is there a way to set a conditional print area? I have a spread sheet that has several sections and want to print based on a formula. If anyone can help thanks in advance! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
set area in excel not giving me option to set print area? | Excel Discussion (Misc queries) | |||
File, print area, clear area, is not working | New Users to Excel | |||
print area across the freeze panes area | Excel Worksheet Functions | |||
Conditional print area | Excel Discussion (Misc queries) | |||
How do you turn off a print area for a page? (no print area) | Excel Discussion (Misc queries) |