change print area based on cell value

[ryan_law2000]

Hey Everyone,

Not sure if this is possible, but here we go
if Q7 = any #
that will change the print area.

example
if Q7 = 1 change print area to A1: Q22

if Q7 = 50 then change print area A1:Q71

if Q7 = 51 change print area to A1: Q72
And soo on

Any ideas how this can be done?

 

[Son]

I use use a macro to do this. Here is the code:

Sub test1()
Dim EndOfArea

EndOfArea = Worksheets("Sheet1").Range("Q7")


ActiveSheet.PageSetup.PrintArea = "$A$1:$G" & EndOfArea
End Sub

So, essentially, you declare that the end of the print area would be named EndOfArea, which takes its value from the value of cell Q7. If you want you can write EndOfArea + a number you want.

 

[ryan_law2000]

ok i got that to work,

What code could i use that everytime a # is changed in Q7 that macro will run?

 

[Son]

hmmm, I guess you would need a "change event" in the worksheet code, so everytime the cell q7 changes, then your code runs. Or, you could assign the code to a button, and then press the button before printing

 

[ryan_law2000]

Any idea what the worksheet code would be?

I would assume

Q7 change . run macro test1()

any ideas out there what the full code would be?

 

[Son]

here is the code for the change event, you need to place it to the worksheet code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)   
    If Target.Column = 17 And Target.Row = 7 Then
         Call TestPrintArea
    End If
End Sub

 

[ryan_law2000]

Perfect! Thanks!

 

[Son]

you're welcome, glad to be of assistance!