Macro to Refresh child pivot table

H

HKS

In one workbook, I have three parent pivot tables and ten child pivot tables.
I want a macro to refresh these child tables from different parent tables.
However I always have debug error in the following code. What did I do wrong
here?

Set vTableChild = ActiveCell.PivotTable
Worksheets(vWSMain).PivotTableWizard SourceType:=xlPivotTable,
SourceData:=vTableParent1

Worksheets(vWSChild).PivotTables(vTableChild).PivotCache.BackgroundQuery =
True

Thanks
 
C

Candyman

Now I am pretty rough at this, but the pivot tables are a function of the
worksheet. They can also be named (like a range).
For Each WS In Worksheets
For Each PT In WS.PivotTables
PT.PivotCache.Refresh
Next PT
Next WS

try renaming the Parent pivot tables and then pointing the children to those
sources.
 
H

HKS

Thanks for reply.
I tried your code before, but nothing seem updated.
Could you please update the below sample code about what you said pointing
the child PT to the parent PT?

Worksheets(ParentWorkSheet).Activate
vParentPTname = ActiveSheet.PivotTables(1).Name

For Each WS In Child Worksheets
For Each PT In WS.PivotTables
PT.PivotCache.Refresh
Next PT
Next WS

Thanks!
HKS
 
C

Candyman

well I said i was rough. You may have to reserch Pivots more and how to set
properties. Sorry
Dim vParentPT1 as pivottable
Dim PT2 as pivottable

Worksheets(ParentWorkSheet).Activate
vParentPT1 = ActiveSheet.PivotTables(1)
vParentPT2 = worksheets(2).PivotTables(1)

vParentPTname = vParentPT1.Name
vParentPTname2 = vParentPT2.Name


For Each WS In Child Worksheets
For Each PT In WS.PivotTables
set the property
PT.PivotCache.Refresh
Next PT
Next WS

Sorry this is lame, but I'd hoped to encourage you in some direction.
Maybe someone else out there has more.
 
H

HKS

Thanks Candyman! Next time you shd be rougher, I didn't have any black-eye
yet. I'm still hoping to receive more tips about the pointing the child PT to
the parent PT, I didn't have any luck researching the code yet.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top