Pivot table as dynamic range in VBA

Question

How can a pivot table be programmed as the printing area?

Now I have a macro that generates a pivot table (if there is better code to do this please advice)

On the same sheet as the pivot table there is also other data but only the pivot table needs to be printed.

There seems to be a possibility using dynamic ranges but no idea how to use that to get where I want.

Other methods are also welcome as long as it can be done by the macro.

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1:E" & Range("A" & Columns("A").End(xlDown).Row).Row), Version:=xlPivotTableVersion10).CreatePivotTable _

        TableDestination:=Selection, TableName:="Draaitabel6", _

        DefaultVersion:=xlPivotTableVersion10

    With ActiveSheet.PivotTables("Draaitabel6").PivotFields("Soort kader") 'Range("E1"))

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Draaitabel6").PivotFields("Afmetingen")

        .Orientation = xlRowField

        .Position = 2

    End With

    ActiveSheet.PivotTables("Draaitabel6").AddDataField ActiveSheet.PivotTables( _

        "Draaitabel6").PivotFields("Aantal"), strJobAndProject, xlSum

    ActiveSheet.PivotTables("Draaitabel6").TableStyle2 = "PivotStyleMedium9"

Thanks for any help

Frank

Answer 1

See if this helps

https://www.youtube.com/watch?v=aLkNIVMnAZY

Answer 2

Sorry, not an option.

The pivot table is genereted using an other macro file.

The file containing the pivot table should have no code, only formulas and data, so the print area should be controled by some form of formula (dynamic or something) so that the print area changes when the size of the pivot table changes but whithout using code.

Can this be done?

Answer 3

I am not sure if this will work, try it

ActiveSheet.PivotTables("Draaitabel6").PivotCache.Refresh

ActiveSheet.PivotTables("Draaitabel6").PivotSelect "", xlDataAndLabel, True

ActiveSheet.PageSetup.PrintArea = Selection.Address

Answer 4

 ActiveSheet.PivotTables("Draaitabel6").PivotSelect "", xlDataAndLabel, True

 ActiveSheet.PageSetup.PrintArea = Selection.Address         

This code does set the print area to the pivot table but when the data changes in the table, the print area does'nt change and refers still to the first pivot table size

Is there a way to get the print area to adapt to the changes of the pivot table?

Answer 5

ActiveSheet.PivotTables("Draaitabel6").PivotSelect "", xlDataAndLabel, True

 ActiveSheet.PageSetup.PrintArea = Selection.Address