Results 1 to 3 of 3

Thread: VBA Code to create Pivot tables

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13

    VBA Code to create Pivot tables

    I would like VBA code to delete the Pivot table sheets as Excel does not allow for the raw data to be imported and the raw data is manipulated by deleting unwanted columns and adding some new columns

    I would like VBA code to create two Pivot table sheets-see sheet "Purchases" & "FA-YTD Dep"

    The Pivot Table-"Purchases" must contain the following:Asset Type, Capital Cost, financial Year

    The Pivot Table FA-YTD Dep must contain the following:Asset Type, WDV, Capital Cost, total-dep

    I have attached sample data

    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Howard, try this


    Code:
    Sub PivotTableCreate()
    
        Dim pvtC As PivotCache
        Dim pvt As PivotTable
        Dim wks As Worksheet
        Dim lng As Long
        Const strSheetNames As String = "FA-YTD DEP[]Purchases"
        
        Application.DisplayAlerts = False
        For lng = 0 To UBound(Split(strSheetNames, "[]"))
            Worksheets(Split(strSheetNames, "[]")(lng)).Delete
            With Worksheets.Add
                .Name = Split(strSheetNames, "[]")(lng)
            End With
        Next lng
        Application.DisplayAlerts = True
        
        Set pvtC = ThisWorkbook.PivotCaches.Create(SourceType:=1, SourceData:= _
            Worksheets("Imported Data").UsedRange.AddressLocal(0, 0, xlR1C1, True), Version:=xlPivotTableVersion12)
        Set pvt = pvtC.CreatePivotTable(TableDestination:="'" &  Split(strSheetNames, "[]")(0) & "'!R1C1", TableName:="0",  DefaultVersion:=xlPivotTableVersion12)
    
        With pvt
            With .PivotFields("Asset Type")
                .Orientation = xlRowField
            End With
            With .PivotFields("Financial Year")
                .Orientation = xlPageField
            End With
            .AddDataField .PivotFields("Capital Cost"), "Sum of Capital Cost", xlSum
        End With
            
        Set pvt = pvtC.CreatePivotTable(TableDestination:="'" &  Split(strSheetNames, "[]")(1) & "'!R1C1", TableName:="1",  DefaultVersion:=xlPivotTableVersion12)
    
        With pvt
            With .PivotFields("Asset Type")
                .Orientation = xlRowField
            End With
            .AddDataField .PivotFields("Capital Cost"), "Sum of Capital Cost", xlSum
            .AddDataField .PivotFields("Total-Dep"), "Sum of Total-Dep", xlSum
            .AddDataField .PivotFields("WDV"), "Sum of WDV", xlSum
        End With
        
        Set pvt = Nothing
        Set pvtC = Nothing
        Set wks = Nothing
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Hi Excelfox

    Thanks for the reply & your help. The code works perfectly

    Regards

    Howard

Similar Threads

  1. Excel VBA Code to Add New Sheets
    By cdurfey in forum Excel Help
    Replies: 1
    Last Post: 06-25-2013, 08:05 AM
  2. Replies: 8
    Last Post: 06-08-2013, 01:24 PM
  3. Replace Pivot Table Source Range with New Address VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 04-25-2013, 07:51 PM
  4. Create a Pivot table
    By NITIN SHETTY in forum Excel Help
    Replies: 3
    Last Post: 01-26-2013, 11:01 AM
  5. Reading Access tables using INNER JOIN
    By Rasm in forum Excel Help
    Replies: 2
    Last Post: 02-04-2012, 09:17 PM

Posting Permissions

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