+ Reply to Thread
Results 1 to 1 of 1

Create multiple pivot charts [Error: Method 'SetSourceData' of object '_chart' failed]

Hybrid View

DianCharloValentine Create multiple pivot charts... 09-26-2014, 07:51 AM
  1. #1
    Registered User
    Join Date
    09-26-2014
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    1

    Create multiple pivot charts [Error: Method 'SetSourceData' of object '_chart' failed]

    Firstly I created 4 pivot tables into 1 worksheet named OOTWeeklyTrendperPlatform, OOTWeeklyTrendperFailureMode, OverallWeeklyTrendperDereel, OverallWeeklyTrendperEngDataValue. These portion was completed successfully.

    After that from each of these 4 pivot tables I wanted to create pivot chart. So there is 4 pivot tables and 4 pivot charts inside 1 worksheet.

    My sequence of work is this way (table and chart name is for illustration purpose) table1 then chart1 then table2 then chart2 and so on. The problem is that after completed table1, chart1, and table2; I get an error when producing chart2 (or second chart correspond to table2). Please find shortened code below which shows only steps to create first 2 charts.

    Sub MakePivotTableDereel()
        Dim PTCache As PivotCache, PTCache1 As PivotCache
        Dim PT As PivotTable, PT1 As PivotTable, PT2 As PivotTable, PT3 As PivotTable
    
        Dim rngChart As Range, rngChart1 As Range, rngChart2 As Range, rngChart3 As Range
        Dim objChart As ChartObject, objChart1 As ChartObject, objChart2 As ChartObject, objChart3 As ChartObject
    
        Dim PivotDereel As Worksheet
        Application.ScreenUpdating = False
    
    '   Delete PivotSheet if it exists
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets("PivotDereel").Delete
        On Error GoTo 0
    
    '   Create Pivot Cache
        Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Data").Range("A1").CurrentRegion.Address)
    
    '   Add PivotDereel sheet
        Set PivotDereel = Worksheets.Add
        ActiveSheet.Name = "PivotDereel"
        Cells(1, 1).Value = "OOT Weekly Trend per Platform"
        Cells(1, 1).Font.Size = 16
        Cells(1, 15).Value = "OOT Weekly Trend per Failure Mode"
        Cells(1, 15).Font.Size = 16
        Cells(1, 29).Value = "Overall Weekly Trend per Dereel"
        Cells(1, 29).Font.Size = 16
        Cells(1, 39).Value = "Overall Weekly Trend per Eng Data Value"
        Cells(1, 39).Font.Size = 16
    
    '   Create pivot table OOTWeeklyTrendperPlatform
        Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, TableDestination:=PivotDereel.Cells(4, 1), TableName:="OOTWeeklyTrendperPlatform")
        ' set table properties below
        With PT
            .PivotFields("Work Week").Orientation = xlRowField
            .PivotFields("PLATFORM").Orientation = xlColumnField
            .PivotFields("EngDataValue").Orientation = xlPageField
            .PivotFields("LotID").Orientation = xlDataField
            .DisplayFieldCaptions = False
            .TableStyle2 = "PivotStyleMedium2"
            .PivotFields("Count of LotID").Caption = "Lot ID"
        End With
        ' Filter unwanted data below
        PT.PivotFields("EngDataValue").CurrentPage = "(All)"
        With PT.PivotFields("EngDataValue")
            .PivotItems("BROKEN SEAL").Visible = False
            '.PivotItems("DAMAGED MATERIAL").Visible = False
            .PivotItems("COGHOLE DAMAGE").Visible = False
            '.PivotItems("DEBRIS IN TAPE").Visible = False
            .PivotItems("DRIFTED COVER TAPE").Visible = False
            '.PivotItems("DROPPED REEL").Visible = False
            .PivotItems("EMPTY POCKET").Visible = False
            '.PivotItems("ENGINEERING REQUEST").Visible = False
            .PivotItems("LOOSE COVER TAPE").Visible = False
            .PivotItems("MACHINE COUNT ERROR").Visible = False
            .PivotItems("MACHINE LOCKED UP").Visible = False
            .PivotItems("OUT OF MATERIALS").Visible = False
            .PivotItems("OUT OF PURGE TIME").Visible = False
            '.PivotItems("OVER/UNDER SEALED").Visible = False
            .PivotItems("PART OUT OF POCKET").Visible = False
            '.PivotItems("PO ERROR").Visible = False
            .PivotItems("TECH PURGED SYSTEM").Visible = False
            .PivotItems("THICK/THIN SEAL").Visible = False
        End With
        PT.PivotFields("EngDataValue").EnableMultiplePageItems = True
    
    '   Create pivot chart OOTWeeklyTrendperPlatform
        Debug.Print PT.TableRange2.Columns.Count
        'Use the ChartObjects.Add Method to add an embedded Pivot Chart, which is represented as a ChartObject object. Note that the arguments Left and Width are mandatory to specify in this method. This method allows you to set the position and size (both in points) of the chart.
        Set objChart = Sheets("PivotDereel").ChartObjects.Add(Left:=10, Top:=25 * (PT.TableRange2.Rows.Count), Width:=300, Height:=200)
        'set data source range for the Chart:
        Set rngChart = PT.TableRange2
        With objChart.Chart
        .SetSourceData Source:=rngChart
        End With
    
        With objChart.Chart
            .HasTitle = True
            .ChartTitle.Characters.Text = "OOT Weekly Trend per Platform"
            .ChartTitle.Font.Bold = True
            .ChartTitle.Font.Size = 14
        End With
    
    '   Create pivot table OOTWeeklyTrendperFailureMode
        Set PT1 = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, TableDestination:=PivotDereel.Cells(4, 15), TableName:="OOTWeeklyTrendperFailureMode")
        With PT1
            .PivotFields("Work Week").Orientation = xlRowField
            .PivotFields("Failure Mode").Orientation = xlColumnField
            .PivotFields("EngDataValue").Orientation = xlPageField
            .PivotFields("LotID").Orientation = xlDataField
            .DisplayFieldCaptions = False
            .TableStyle2 = "PivotStyleMedium2"
            .PivotFields("Count of LotID").Caption = "Lot ID"
        End With
        PT1.PivotFields("EngDataValue").CurrentPage = "(All)"
        With PT1.PivotFields("EngDataValue")
            .PivotItems("BROKEN SEAL").Visible = False
            '.PivotItems("DAMAGED MATERIAL").Visible = False
            .PivotItems("COGHOLE DAMAGE").Visible = False
            '.PivotItems("DEBRIS IN TAPE").Visible = False
            .PivotItems("DRIFTED COVER TAPE").Visible = False
            '.PivotItems("DROPPED REEL").Visible = False
            .PivotItems("EMPTY POCKET").Visible = False
            '.PivotItems("ENGINEERING REQUEST").Visible = False
            .PivotItems("LOOSE COVER TAPE").Visible = False
            .PivotItems("MACHINE COUNT ERROR").Visible = False
            .PivotItems("MACHINE LOCKED UP").Visible = False
            .PivotItems("OUT OF MATERIALS").Visible = False
            .PivotItems("OUT OF PURGE TIME").Visible = False
            '.PivotItems("OVER/UNDER SEALED").Visible = False
            .PivotItems("PART OUT OF POCKET").Visible = False
            '.PivotItems("PO ERROR").Visible = False
            .PivotItems("TECH PURGED SYSTEM").Visible = False
            .PivotItems("THICK/THIN SEAL").Visible = False
        End With
        PT1.PivotFields("EngDataValue").EnableMultiplePageItems = True
    
    '   Create pivot chart OOTWeeklyTrendperFailureMode
    
        'Use the ChartObjects.Add Method to add an embedded Pivot Chart, which is represented as a ChartObject object. Note that the arguments Left and Width are mandatory to specify in this method. This method allows you to set the position and size (both in points) of the chart.
        Set objChart1 = Sheets("PivotDereel").ChartObjects.Add(Left:=10, Top:=25 * (PT1.TableRange2.Rows.Count), Width:=300, Height:=200)
        'set data source range for the Chart:
        Set rngChart1 = PT1.TableRange2
    
        With objChart1.Chart
        .SetSourceData Source:=rngChart1
        End With
    
        With objChart.Chart
            .HasTitle = True
            .ChartTitle.Characters.Text = "OOT Weekly Trend per Platform"
            .ChartTitle.Font.Bold = True
            .ChartTitle.Font.Size = 14
        End With
    'below is the portion to create 3rd and 4th pivot tables and charts
    end sub
    I get error: Method 'SetSourceData' of object '_chart' failed at part,
    With objChart1.Chart
            .SetSourceData Source:=rngChart1
    End With
    Last edited by DianCharloValentine; 09-26-2014 at 07:52 AM. Reason: text adjustment

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Runtime Error '1004': Method 'Pivot Tables' of object '_Worksheet' Failed
    By ZuneidDassu in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-07-2014, 06:21 AM
  2. method 'location' of object '_Chart' failed.
    By Hendel in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-29-2008, 01:03 PM
  3. Method 'SeriesCollection' of object '_Chart'failed
    By Dennis Mak in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2006, 02:55 AM
  4. Run-time error '1004'; Method 'Export' of object'_Chart' failed
    By Father Guido in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2005, 10:35 AM
  5. Run-time error 1004: Method 'Axes' of object '_chart' failed
    By Brian Morrison in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-20-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1