Macro Update Pivot Chart (VBA Excel 2003)

Topic

New Reply

Hi,

Im just putting together a pivot chart that takes data from two worksheets. With the help of the Pivot wizard the table works well. The problem is the data Im using now is likely to change on a day by day basis. The idea being that the user hits a toolbar button which brings in data to excel from access and the pivot chart updates automatically and is presented to the user for analysis.

Ive recorded a macro to see how the pivot chart is put together but despite numerous attempts I cant modify the part of the macro to test where the data in the two source data sheets ( called ‘Data Sheet’ & ‘Thet_Data’), finishes.

I’ve provided the pivot chart macro below;

Sheets(“Sheet2”).Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
Array(Array(“‘Data Sheet’!R1C1:R948C2”, “Item1”), Array(“Thet_Data!R1C1:R241C2”, _
“Item2″))).CreatePivotTable TableDestination:=””, TableName:=”PivotTable2″ _
, DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables(“PivotTable2”).DataPivotField.PivotItems( _
“Count of Value”).Position = 1
Charts.Add
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlColumnClustered

Does anyone have any suggestions please?? Any help will be grately appreciated.

Regards
Lee

Reply | Quote

Replies

You could use this, perhaps:

Dim m1 As Long
Dim m2 As Long

m1 = Worksheets(“Data Sheet”).Range(“A” & Rows.Count).End(xlUp).Row
m2 = Worksheets(“Thet_Data”).Range(“A” & Rows.Count).End(xlUp).Row

… SourceData:= _
Array(Array(“‘Data Sheet’!R1C1:R” & m1 & “C2”, “Item1”), Array(“Thet_Data!R1C1:R” & m2 & “C2”, _
“Item2”)))…

Reply | Quote

Hi Hans,

Many thanks for that. I’ll give it a go and post back how I get on.

Thanks again.

Regards.
Lee

Reply | Quote