CODEWITHSUNDEEP
vbaexcelpivotpivot-table
4d554d424c4553
4d554d424c4553

Reputation: 95

Copy Data from Pivot table to new sheet

Hi Have a VBA script that very simply copy's data from my totals in the pivot table and past them into another sheet. Code:

Public Sub Update_Tracker_TS_Custody()
Sheets("TS_Custody_Pivot").Select
Sheets("TS_Custody_Pivot").Range("B05:C05").Copy
Sheets("TS_Custody_Tracking").Cells(Rows.Count, "C").End(xlUp).Offset(1). _
PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("TS_Custody_Tracking").Select
n = Cells(Rows.Count, "C").End(xlUp).Row
Range("A" & n) = Date
Range("B" & n) = Time End Sub

My issues is that my Pivot table states can change so one day i could have 5 different state totals and another with only 4 in a different order from when it was 5 States. So when the VBA script copy's onto the new sheet it dose not line up correctly under all the states

e.g. Day 1

State 1 State 2 State 3 State 4 State 5

Day 2

State 1 State 2 State 3 State 5

How can i get the VBA script to copy and match so if state 4 is missing it will still copy 1,2,3,5 under the right column the other sheet and put a 0 in state 4.

Upvotes: 0

Views: 6944

Answers (1)

4d554d424c4553
4d554d424c4553

Reputation: 95

I have been able to sort this my self by simply created another table from the pivot table using

=IFERROR(GETPIVOTDATA("State",$A$3,"State","New"),0)

and then using the macro to copy the data from the table

Upvotes: 1

Related Questions