VBA Code to create Pivot tables

[Howardc]

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

[Excel Fox]

Howard, try this

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

[Howardc]

Hi Excelfox

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

Regards

Howard