Need help with excel charts using dynamic range

[Sanjeev Satia]

Hi,

I am facing this issue with an excel chart which is populated using a dynamic range of cells.

Scenario:

There are 3 sheets in the workbook -

Raw Data - Contains the data
Formatted - Contains pivot table and range
Summary - Contains the final excel chart

The chart on the "Summary" tab is formatted in a specific way which my customer likes. This chart uses the source data from "Formatted" tab. The data is dynamic which means the number of products can increase or decreas and accordingly chart should take less or more values.

I use Raw Data to make a pivot table on "Formatted" tab and then copy paste special that pivot table in a differrent place in that same sheet and use that as source data for chart as mentioned above.

I have written the below code for this:

Sub CarePack_Pivot()
Dim PT As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Raw Data")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Formatted")
Dim PTCache As PivotCache
Dim PRange As Range
Dim pf As PivotField
Dim WSD1 As Worksheet
Set WSD1 = Worksheets("Care Pack Pivot")

' Delete any prior pivot tables
For Each PT In WSD1.PivotTables
PT.TableRange2.Clear
Next PT
' Find the last row with data
Dim FinalRow As Long
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

' Find the last column with data
Dim FinalCol As Long
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column

' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

' Create the pivot table
Sheets("Care Pack Pivot").Select
Set PT = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 2), _
TableName:="CarePackPivot")

' Define the layout of the pivot table

' Set update to manual to avoid recomputation while laying out
PT.ManualUpdate = True

' Set up the row fields
PT.AddFields RowFields:=Array( _
"Year")

With PT.PivotFields("Pack Serial Number")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With

' Calc the pivot table
PT.ManualUpdate = False

' Copy and paste the pivot table
PT.TableRange2.Select
Selection.Copy

Range("B40").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Range("A1").Select

End Sub


But everytime my pivot has more values or less - the chart linked on the "Summary" page goes for a toss.

Can someone please help. I really appreciate it.

Thanks,
Sanjeev

 

[Sanjeev Satia]

The pivot table is created starting from cell B1 of the "Formatted" tab and the same is copy and pasted in cell A40 of the same tab as source data for the final chart.

 

[Sanjeev Satia]

Is there no one who can help??