Set Different Print Areas for Multiple Sheets

[scottclayton]

Greetings Lovers of Excel,

In making my label printer, I will need to set the print area for multiple sheets based on a range generated in a cell. E.g. Sheet1 might need cells A1:P1200 set as print area, Sheet2 might need cells A1:G694 set as the print area, Sheet3 might not need to be printed, etc..

I have no problem generating a formula to state what the print area for each sheet should be, respectively, but passing that info into VBA has me really stumped. (Using indirect in the Page Setup menu doesn't work after the value has changed—it changes the value to a static reference, which is bizarre). I suspect I'd want to use the Sub Workbook_BeforePrint, but I'm not 100% on that. Anyone have any ideas? I will generally have 3 or more specific sheets selected—using a button-triggered macro that gets a cell value of sheet names and selects those sheets. Any ideas for different ways of going about this if my above-mentioned idea seems like it could be supplanted?

Thanks!

 

[Andrew Poulsom]

What have you tried in VBA? If a1 contains a valid range reference like $B$1:$C$10 you should be able to use:

Sub Test()
    With ActiveSheet
        .PageSetup.PrintArea = .Range("A1").Value
    End With
End Sub

 

[scottclayton]

Thanks for the reply, Andrew Poulsom.

Although that suggestion works for one sheet, it doesn't work when there are multiple sheets selected—it will only affect the active sheet of the selected sheets. It would be very elegant if it was a set of instructions that says "for the whole workbook, each sheets print area is given in its respective cell Q1" or something, but I don't know if there's a sub that could work like this.

 

[Andrew Poulsom]

You have to set the print area one sheet at a time. Try this:

Sub Test()
    Dim Sh As Object
    For Each Sh In ActiveWindow.SelectedSheets
        With Sh
            .PageSetup.PrintArea = .Range("A1").Value
        End With
    Next Sh
End Sub

 

[scottclayton]

Andrew Poulsom,

That's perfect, and so elegant!

Thank you so much. Loops are so powerful.