+ Reply to Thread
Results 1 to 2 of 2

Trying to create a pivot table with a script but I get nothing

Hybrid View

  1. #1
    Registered User
    Join Date
    07-15-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010 & 365
    Posts
    1

    Question Trying to create a pivot table with a script but I get nothing

    Hi all,
    I'm trying my hand at PivotTables in VBA for the first time and I'm stuck.
    I feel like there's something obvious that's missing, but I can't figure out for the life of me
    The code is supposed to insert a specific sheet for the pivot(s)- there'll be more when I figure out how - and insert a pivot table with table filters (2 of them) and nicely grouped dates by month (and year).
    The blank sheet is inserted, but... nothing happens... no pivot table appears, and no error in VBA

    Greatly appreciated if anyone had an idea...
    Many thanks in advance.

    The code I've got so far is:
    Sub CreatePivotTable01()

    Dim DataSheet As Worksheet
    Dim LRow, LCol As Long
    Dim BondP, PelorisP As Worksheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim df As PivotField
    Dim PRange As Range
    Dim wb As Workbook
    Set wb = ActiveWorkbook

    'Declare variables
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("BOND Pivots2").Delete
    Sheets.Add After:=Sheets("ProcessedData")
    ActiveSheet.Name = "BOND Pivots2"
    Application.DisplayAlerts = True

    Set BondP = Worksheets("BOND Pivots2") 'Sheet for BOND Pivots
    Set DataSheet = Worksheets("ProcessedData") 'Data sheet

    'Define Data Range
    LRow = DataSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LCol = DataSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DataSheet.Cells(1, 1).Resize(LRow, LCol)

    'Define the cache for the PivotTable 2
    Set PCache = wb.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:=PRange)

    'Insert Blank Pivot Table
    'Set PTable = PCache.CreatePivotTable _
    '(TableDestination:=BondP.Cells(5, 12), TableName:="BP1")

    'Create the PivotTable 2
    Set PTable = wb.PivotTables.Add(PivotCache:=PCache, _
    TableDestination:=wb.Range("A1"), TableName:="BP1")
    Debug.Print (TableDestination.Address)

    '& insert the fields 2
    PTable.AddFields RowFields:="Created Date", ColumnFields:="Object Code Description"
    PTable.AddDataField Field:=PTable.PivotFields("Object Code Description"), _
    Caption:="Paretos", Function:=xlCount

    'Group Date field by Month and year
    PTable.RowAxisLayout xlTabularRow
    Set df = PTable.PivotFields("Created Date")
    df.LabelRange.Group Start:=True, End:=True, _
    Periods:=Array(False, False, False, False, True, False, True)
    df.Caption = "Month"

    'Format Pivot Table
    ActiveSheet.PivotTables("BP1").ShowTableStyleRowStripes = True
    ActiveSheet.PivotTables("BP1").ShowTableStyleColumnStripes = True
    ActiveSheet.PivotTables("BP1").TableStyle2 = "PivotStyleMedium9"
    ActiveSheet.PivotTables("BP1").DataBodyRange.DateFormat = "D MMM YY"

    End Sub

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Trying to create a pivot table with a script but I get nothing

    Have you tried recording a macro while creating the pivot table manually to get the correct syntax?
    Martin

+ 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. [SOLVED] VBA create pivot table in excel 2010 defaulted to 2003 pivot table format
    By lynnsong986 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2019, 02:05 PM
  2. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  3. Macro script to create pivot tables
    By marielouise1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-05-2013, 01:21 PM
  4. [SOLVED] Create Pivot Table: Cannot Open Pivot Table Source File
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-05-2010, 05:11 AM
  5. Pivot Table Script Problem
    By realniceguy5000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-20-2009, 10:32 AM
  6. Pivot Table Script Change
    By realniceguy5000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-18-2008, 12:10 PM

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