VBA to select multiple dynamic ranges and set its print area

[crakkus]

Hello,

I have about 10 pivot tables in one sheet moving across the sheet from A1 to BM1. I am trying to create a macro that will select the top cell of each pivot table, ctrl+shift+down to select its dynamic depth, set the selection to an object, and then set the print area using a union. In theory it all makes sense but when I get to the union I get an error 438. Any reason why my code doesn't seem to work? Thank you in advance!

Sub PrintArea()
'
' PrintArea Macro
'
Dim myCells1 As Range
Dim myCells2 As Range
Dim myCells3 As Range
Dim myCells4 As Range
Dim myCells5 As Range
Dim myCells6 As Range
Dim myCells7 As Range
Dim myCells8 As Range
Dim myCells9 As Range
Dim myCells10 As Range
Dim Total1 As Range


'
    Range("A1:G27").Select
    Range(Selection, Selection.End(xlDown)).Select
        Set myCells1 = Selection
    Range("H1:N1").Select
    Range(Selection, Selection.End(xlDown)).Select
        Set myCells2 = Selection
    Range("O1:U1").Select
    Range(Selection, Selection.End(xlDown)).Select
        Set myCells3 = Selection
    Range("V1:AB1").Select
    Range(Selection, Selection.End(xlDown)).Select
        Set myCells4 = Selection
    Range("AC1:AI1").Select
    Range(Selection, Selection.End(xlDown)).Select
        Set myCells5 = Selection
    Range("AJ1:AP1").Select
    Range(Selection, Selection.End(xlDown)).Select
        Set myCells6 = Selection
    Range("AQ1:AW1").Select
    Range(Selection, Selection.End(xlDown)).Select
        Set myCells7 = Selection
    Range("AX1:BD1").Select
    Range(Selection, Selection.End(xlDown)).Select
        Set myCells8 = Selection
    Range("BE1:BK1").Select
    Range(Selection, Selection.End(xlDown)).Select
        Set myCells9 = Selection
    Range("BM1:BR14").Select
        Set myCells10 = Selection
    'Set Total1 = .Range(myCells1, myCells2, myCells3, myCells4, myCells5, myCells6, myCells7, myCells8, myCells9, myCells10)
    'ActiveSheet.PageSetup.PrintArea = Range(Total1).Address
    With ActiveSheet
        .PageSteup.PrintArea = Union(myCells1, myCells2, myCells3, myCells4, myCells5, myCells6, myCells7, myCells8, myCells9, myCells10).Address
    End With
End Sub

 

[GWteB]

The Union statement isn't the problem, a typo is.

  .PageSteup.PrintArea = Union(myCells1, myCells2, myCells3, myCells4, myCells5, myCells6, myCells7, myCells8, myCells9, myCells10).Address

Change it to .PageSetup and it works.

 

[crakkus]

omg.... thank you so much. lol

 

[GWteB]

Sometimes small mistakes lead to mysterious results. You are welcome!