Macro to Refresh child pivot table
 

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).Pivo tCache.BackgroundQuery =
True

Thanks

Macro to Refresh child pivot table
 

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.
"HKS" wrote:

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).Pivo tCache.BackgroundQuery =
True

Thanks

Macro to Refresh child pivot table
 

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

"Candyman" wrote:

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.
"HKS" wrote:

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).Pivo tCache.BackgroundQuery =
True

Thanks

Macro to Refresh child pivot table
 

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.


"HKS" wrote:

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

"Candyman" wrote:

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.
"HKS" wrote:

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).Pivo tCache.BackgroundQuery =
True

Thanks

Macro to Refresh child pivot table
 

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.

"Candyman" wrote:

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.


"HKS" wrote:

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

"Candyman" wrote:

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.
"HKS" wrote:

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).Pivo tCache.BackgroundQuery =
True

Thanks