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

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

Please Login or Register  to view this content.

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