Transfer pivot table data to a pre-defined template

[sn152]

Hi All,

Happy New Year!

In the attached work book, I have the in the "Data" sheet. I have a code to create pivot tables. The pivot tables are created in 2 seperate sheets namely "Category Pivot" and "Type Pivot".

Now what I want is that the data in the pivot table has to be transferred to the "Template" sheet, in the correct tabular columns.

Please help me with this.

Thanks!

 

[vletm]

sn152
Why this need to do with Macro?
You could do it with formulas too ...
(and if needed, hide those help values ...)

 

[sn152]

Thanks for your reply.

But the pivot range will not be the same always. It will be different everytime. I dont want to manually change the formula in "Template" sheet everytime. Also the values that shown in Type Wise Data tabular columns are incorrect
That is why I wanted to do this with VBA.

Please help.

 

[vletm]

1) Explain which part would vary with Pivot and how?
2) Which part would You need to manually change the formulas?
3) ... incorrect ... typo ... now correct!

 

[p45cal]

Perhaps some variant of one of these:

Sub blah1()
Set TeamsRng = Sheets("Type Pivot").Range("A3").PivotTable.PivotFields("Team").DataRange
RwCnt = TeamsRng.Rows.Count
With Sheets("Template")
  .Range("C4").Resize(RwCnt).Value = TeamsRng.Value
  .Range("I4").Resize(RwCnt).Value = TeamsRng.Value
  .Range("D4:G4").Resize(RwCnt).FormulaR1C1 = "=IFERROR(GETPIVOTDATA(""Job ID"",'Type Pivot'!R3C1,""Team"",RC3,""Type"",R3C),"""")"
  .Range("J4:N4").Resize(RwCnt).FormulaR1C1 = "=IFERROR(GETPIVOTDATA(""Job ID"",'Category Pivot'!R3C1,""Team"",RC9,""Category"",R3C),"""")"
  .Range("D4:G4").Offset(RwCnt).FormulaR1C1 = "=SUM(R[-" & RwCnt & "]C:R[-1]C)"
  .Range("D4:G4,J4:N4").Offset(RwCnt).FormulaR1C1 = "=SUM(R[-" & RwCnt & "]C:R[-1]C)"
End With
End Sub

Sub blah2()
Set TeamsRng = Sheets("Type Pivot").Range("A3").PivotTable.PivotFields("Team").DataRange
RwCnt = TeamsRng.Rows.Count
With Sheets("Template")
  .Range("C4").Resize(RwCnt).Value = TeamsRng.Value
  .Range("I4").Resize(RwCnt).Value = TeamsRng.Value
  With .Range("D4:G4").Resize(RwCnt)
    .FormulaR1C1 = "=IFERROR(GETPIVOTDATA(""Job ID"",'Type Pivot'!R3C1,""Team"",RC3,""Type"",R3C),"""")"
    .Value = .Value
  End With
  With .Range("J4:N4").Resize(RwCnt)
    .FormulaR1C1 = "=IFERROR(GETPIVOTDATA(""Job ID"",'Category Pivot'!R3C1,""Team"",RC9,""Category"",R3C),"""")"
    .Value = .Value
  End With
  With .Range("D4:G4").Offset(RwCnt)
    .FormulaR1C1 = "=SUM(R[-" & RwCnt & "]C:R[-1]C)"
    .Value = .Value
  End With
  With .Range("J4:N4").Offset(RwCnt)
    .FormulaR1C1 = "=SUM(R[-" & RwCnt & "]C:R[-1]C)"
    .Value = .Value
  End With
End With
End Sub

See attached.

 

[GraH - Guido]

Hello sn152

I do not know, but I created a pivot table that looks likes your template.


As you can see the pivot is "static" even while I have used a slicer. Yet the data values in the grid are updated.

Isn't this all you need?

You can make the pivot behave like this with this option for each of the used labels:


Kr
Guido

 

[sn152]

Thank you all for your help.