Dynamic Pivot Table Code

[mt03530]

Hello!

I am attempting to create a dynamic code to create a Pivot Table. I run multiple reports at work and each needs the same Pivot Table, so I'm trying to save myself some time.

The issue I'm encountering is the SourceData. I cannot designate a particular Table because that will change from report to report. I attempted to replace the source with a range, but that did not work.

Any assistance would be greatly appreciated!

    Sheets.Add
    ActiveSheet.Name = "In Program Pivot"


[COLOR=#008000]Dim tri As Long[/COLOR]
[COLOR=#008000]tri = Sheets("In Program").Select.Range("A1:O" & Range("O" & Rows.Count).End(xlUp).Row)[/COLOR]


Sheets("In Program Pivot").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, [COLOR=#008000]SourceData:= _[/COLOR]
[COLOR=#008000]        "tri"[/COLOR], Version:=xlPivotTableVersion15).CreatePivotTable TableDestination _
        :="'In Program Pivot'!R3C1", TableName:="PivotTable1", DefaultVersion:= _
        xlPivotTableVersion15
    Sheets("In Program Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Member Name")
        .Orientation = xlRowField
        .Position = 1
    End With

 

[oldbrewer]

if each report needs the same pivot table, are you saying the pivot table should access data from different sources ? If so I would think you need one defined table for the pivot data, and dynamically change the data within that table. If this takes you 2 hours to design, you could set up a lot of pivot tables in that time. But maybe I am missing something....

 

[mt03530]

Sorry I was not clear.

I have multiple reports that I run all day everyday, so I'm trying to make a macro that creates the same formatted Pivot Table for each of my reports.

For example, I have Report 1 that needs a Pivot Table with variables P thru Z in tabular form, then I have Report 2 that needs a separate Pivot Table with variables P thru Z in tabular form, and then then I have Report 3 that needs yet another Pivot Table with variables P thru Z in tabular form. Each variable has specific formatting plus more general formatting, so I'm oversimplifying for this example.
If I can just run one template macro to create an individual Pivot Table for each workbook/report I have, it can save me some hassle. I can run up to 34 reports a day and each needs their own Pivot Table...

Does that make more sense?