Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Macro Update Pivot Chart (VBA Excel 2003)

    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:=xlConso lidation, 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").DataPivotFi eld.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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro Update Pivot Chart (VBA Excel 2003)

    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")))...

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Macro Update Pivot Chart (VBA Excel 2003)

    Hi Hans,

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

    Thanks again.

    Regards.
    Lee

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •