vba - best practice for re-setting the data range for pivots

[proctorar]

Hello: I have a workbook that has many worksheet. Each worksheet has one pivot.
The datasource for the pivots is a range - pulled from another worksheet in the same workbook.
I have a macro that gets new records from an sql server and appends them to a queryTable for the datasource,
and so I need to re-set the data range for my pivots when this is run.

My code is working; however, it takes 3 min to run. I have 7 worksheets so far. I was wondering if
my code looked most efficient....maybe there's a way to help it to run a bit faster?

thank you for your help,
Proctor

Sub RefreshALLPivotTables()
 Dim WS As Worksheet
 For Each WS In ThisWorkbook.Worksheets
        For Each pt In WS.PivotTables
        'MsgBox ("pt.name " & pt.Name)
            pt.SourceData = Sheets("ShipData").[ShipData!$A$1:$W$1].CurrentRegion.Address(True, True, xlR1C1, True)
            'pt.RefreshTable
        Next pt
        Set pt = Nothing
 Next WS
End Sub

[mikeTRON]

So I started using Dynamic Named Ranged instead of VBA to reset the names.

Named Range
PivotDATA = offset(A1,0,0,counta(A:A),counta(1:1))

Then I just link the pivot to the PivotDATA named range and I never think about it again.

[proctorar]

NameManager.JPG

mikeTRON: Thank you so much for your post!!! I gave it a try and it works great!

Just to verify, after I run my vba code that appends data to my Dynamic named range, I need to click the refresh button to
have that data show up in my pivots?

I wanted to verify, because when I don't hit refresh, it doesn't show up....believe this is how it's suppose to work.

There's one concern, after I run my vba code that appends data, if I look under Formulas-> named manager (see pic)
I noticed that along with my newly created Dynamic named range "ShipData", it was creating another Dynamic named range. When I clicked in the "Refers to:"
textbox, I noticed it was referring to the newly appended data; however, I don't believe I need this named range, because my "ShipData" gets everything.

I stepped through my code and noticed it was adding this named range when it hits the line "Refresh BackGroundQuery: False"
I need this line in order for the new data to appear in the datasheet.

I wanted to know what your thoughts are on it.

Thank you again for providing me with this solution.
Proctor

[mikeTRON]

So I cant really help diagnose your problem, but I do know once you setup the dynamic named range, all you have to do is update the data and

thisworkbook.refreshall

to have the pivot table update.


I would remove this from the code after you initially setup the datasource:

pt.SourceData = Sheets("ShipData").[ShipData!$A$1:$W$1].CurrentRegion.Address(True, True, xlR1C1, True)