Reset a volatile print area

[olebucky]

In advance, thanks so much for your time and attention.

I am using a pivot table to query a large spreadsheet for select data. The pivot table is always four columns wide but can vary widely in length. I have recorded a macro to select the appropriate area to print, format the selected area however, I am not able to get the macro to "re-define" the print area after the pivot table is refreshed and the appropriate area is selected. The following code does all the above stated tasks nicely but the ActiveSheet.PageSetup.PrintArea = "$B$10:$E$19" line fails me. Clearly I need help getting around the absolute range reference.

Thanks again.

Sub RefreshPivotClassType()
'
' RefreshPivotClassType Macro
'

'
    ActiveSheet.PivotTables("PivotClassType").PivotCache.Refresh
    Columns("B:B").Select
    Selection.ColumnWidth = 15
    Columns("C:C").Select
    Selection.ColumnWidth = 15
    Columns("D:D").Select
    Selection.ColumnWidth = 45
    Columns("E:E").Select
    Selection.ColumnWidth = 9
    Range("E9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveSheet.PageSetup.PrintArea = "$B$10:$E$19"
    Range("B9").Select
End Sub

[protonLeah]

You should post a workbook; however you might try:

Dim PrtArea As String
ActiveSheet.PivotTables("PivotClassType").PivotCache.Refresh

'if B10 is the upper left corner of the PT

PrtArea = Range("B10").currentregion.address     
...
etc...
...
ActiveSheet.PageSetup.PrintArea = PrtArea
...
etc...

[jolivanes]

or maybe

ActiveSheet.PageSetup.PrintArea = "$B$10:$E$" & Range("E" & Rows.Count).End(xlUp).Row

Have a quick read here.
http://stackoverflow.com/questions/1...cel-vba-macros

http://www.businessprogrammer.com/po...-using-select/

http://dailydoseofexcel.com/archives...-and-activate/

[olebucky]

Your suggestion worked well. I also thank you for the reading material. My dabblings in VBA clearly pointed to the abhorrence of .Select but the readings confirmed it and gave me viable options. Thanks so much.

[olebucky]

You should post a workbook; however you might try:

Dim PrtArea As String
ActiveSheet.PivotTables("PivotClassType").PivotCache.Refresh

'if B10 is the upper left corner of the PT

PrtArea = Range("B10").currentregion.address     
...
etc...
...
ActiveSheet.PageSetup.PrintArea = PrtArea
...
etc...

I truly thought I should (post a workbook) but it would have required a full rebuild as the data in the workbook is confidential... no, very confidential.

That stated, I used your suggestion and it worked as you thought it might. jolivanes suggestion worked as well. I am a power Excel user when it comes to formulas and other "internal" Excel features. However, I have only dabbled in VBA. The myriad of coding options is often daunting to a VBA greenhorn like me. The help offered quickly and succinctly by the forum regulars is not only immeasurably helpful, but also refreshing. Thank you all so much.