Setting Print Area

[Mike]

Hi,
Is it possible to set/reset the print area according to a cell value?

e.g.:-
if A1 = 1 then set print area to B1:B10
if A1 = 2 then set print area to C1:C10
etc.
TIA
Mike

 

[Don Guillett]

Right click sheet tab>view code>insert this. Now when you change cell a1 the
column range will print automatically. After testing change printpreview to
printout

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range(Cells(1, Target), Cells(10, Target)).PrintPreview
End Sub

 

[Mike H]

You could do this

Alt+f11 to open VB editor. Double click 'This workbook' and paste this in

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Sheets("Sheet1").Range("A1").Value = 1 Then
ActiveSheet.PageSetup.PrintArea = "$B$1:$B$10"
ElseIf Sheets("Sheet1").Range("A1").Value = 2 Then
ActiveSheet.PageSetup.PrintArea = "$C$1:$C$10"
Else
Cancel = True
End If
End Sub

Mike

 

[Don Guillett]

for one column to the right
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range(Cells(1, Target+1), Cells(10, Target+1)).PrintPreview
End Sub