Pivot Sheet in Macro is referring to old data range - causing problems with refresh.

[TheRedCardinal]

My macro works like this:

1. Source workbook has a procedure that copies a table from it, into an existing template file called "Model Workbook"
2. In the model workbook is a pivot table, whose data source is a named range called "PivotData"
3. The model workbook is then saved using a filename depending on the parameters of the file itself
4. The table is modified by the user and when happy, clicks a button called refresh
5. Refresh calls a Sub that checks the table in various ways and then labels the whole range "PivotData"
6. The idea being that then when the PivotTable is refreshed, it brings in the new data instead of the old

BUT!

When I attempt to refresh the Pivot, the data source there is linking to the file "Model Workbook" and the old cell range that made up PivotData rather than the PivotData range in the existing workbook.
The Sub crashes because it can't access that range.

Other than a process to create a whole new pivot table (which I could do but seems unnecessary), is it possible to modify the way the Pivot Table is refreshing inside the new workbook?

 

[dnorm]

Hi Red Cardinal

I am not sure if this will help you fully, but please have a look at the below ideas:

Other than a process to create a whole new pivot table (which I could do but seems unnecessary), is it possible to modify the way the Pivot Table is refreshing inside the new workbook?

If you select a cell in the pivot table then look at the "refresh all" in the ribbon select the drop down, connection properties, there is the option to have the pivot table refresh automatically every 60 seconds or so.

Sorry I cannot help you more. Only thing I can think of is to use a vba to determine your table data, and perform the pivot for you. something like this:

Sub Top_5
'Plant Top 5 Faliure Mode Pivot:

Dim PvtTbl5 As PivotTable
Set PvtTbl5 = Worksheets("TOP5").PivotTables("PivotTable5")

PvtTbl5.ClearAllFilters
'set 3 non-manual filters:
PvtTbl5.PivotFields("Failure Mode").PivotFilters.Add Type:=xlTopCount, DataField:=PvtTbl5.PivotFields("Sum of Cost Tot"), Value1:=5
PvtTbl5.PivotFields("Item Number").PivotFilters.Add Type:=xlTopCount, DataField:=PvtTbl5.PivotFields("Sum of Cost Tot"), Value1:=1
PvtTbl5.PivotFields("Eff Date").PivotFilters.Add Type:=xlSpecificDate, Value1:=Range("K1")
ActiveSheet.PivotTables("PivotTable5").PivotFields("Shift").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Shift")
.PivotItems("0").Visible = False
.PivotItems("(blank)").Visible = False
End With

    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Failure Mode")
.PivotItems("End of Roll").Visible = False
.PivotItems("Extruder - Backing joint").Visible = False
.PivotItems("Extruder - Roll Allowanc").Visible = False
.PivotItems("Extruding - Leader Joint").Visible = False
.PivotItems("ROLL ALLOWANCE").Visible = False
.PivotItems("Stitch Join").Visible = False
.PivotItems("Stitch Joint").Visible = False
.PivotItems("Tuft Colour Change").Visible = False
.PivotItems("Colour Change Tufter 1").Visible = False
.PivotItems("Colour Change Tufter 2").Visible = False
.PivotItems("Colour Change Tufter 3").Visible = False
.PivotItems("Colour Change Tufter 4").Visible = False
.PivotItems("SET UP SCRAP TUFTER 1").Visible = False
.PivotItems("SET UP SCRAP TUFTER 2").Visible = False
.PivotItems("SET UP SCRAP TUFTER 3").Visible = False
.PivotItems("SET UP SCRAP TUFTER 4").Visible = False
.PivotItems("Ext - Backing joint CSH").Visible = False
.PivotItems("Ext - Backing joint HH").Visible = False

    End With

End Sub

This creates and updates one of the pivots (as the name says I have 5) to give me a top 5 result for fails, top one result in that for items and the cost of said failure. then goes on to filter all the unwanted faults out. my fields look like this:

I hope this helps.