if --------
then
ActiveSheet.PageSetup.PrintArea = -------
end if
Private Sub Workbook_BeforePrint(Cancel As Boolean)
if activesheet.range ("A1").value = 2
then
ActiveSheet.PageSetup.PrintArea = range ("a1:d10")
end if
End Sub
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
Thank You
Learning VBA
"Mike H" <Mi...@discussions.microsoft.com> wrote in message
news:95FD5310-9447-4717...@microsoft.com...
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.
--
Dave Peterson
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 here:
http://contextures.com/xlNames01.html#Dynamic
--
Dave Peterson
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!
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.
--
Dave Peterson