Creating a Pivot Table with VBA

[stinkingcedar]

Hey guys,

I am trying to create a pivot table with VBA based off of some data that I have. Here is my code:

Option Explicit

Dim dolSht As Worksheet, hourSht As Worksheet
Dim sumSht As Worksheet, byCustSht As Worksheet, allCostSht As Worksheet
Dim ptCache As PivotCache
Dim pt As PivotTable
Dim ptRange As Range
Dim lastRow As Long, lastColumn As Long


Public Sub CreatPivot()


Set dolSht = Sheets("Dollars")
Set hourSht = Sheets("Hours")
Set sumSht = Sheets("Summary")
Set byCustSht = Sheets("by Customer")
Set allCostSht = Sheets("All Costs")


For Each pt In sumSht.PivotTables


    pt.TableRange2.Clear
    
Next pt


lastRow = dolSht.Cells(Application.Rows.Count, 1).End(xlUp).row
lastColumn = dolSht.Cells(1, Application.Columns.Count).End(xlToLeft).Column


Set ptRange = dolSht.Cells(1, 1).Resize(lastRow, lastColumn)
Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ptRange.Address, Version:=xlPivotTableVersion14)


Set pt = ptCache.CreatePivotTable(TableDestination:=sumSht.Cells(60, 1), TableName:="PivotTable1")


pt.AddFields RowFields:=Array("Market", "REGION", "Business Type"), ColumnFields:="ZZTYPE"


With pt.PivotFields("TOT DOLLARS")
    
    .Orientation = xlDataField
    .Function = xlSum
    .Position = 1
    .NumberFormat = "#, ##0"
    .Name = "TOT DOLLARS "
    
End With


End Sub

I keep getting several errors. Firstly, on the line the sets the "pt" variable and creates the pivot table, I will sometimes get a long error message saying "The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."

I will also sometimes get "Method 'CreatePivotTable' of object 'PivotCache' failed" on this line.

Also, I keep getting the error type 91 "Object variable or With block variable not set" on the line of code right below this were the fields are added to the Pivot Table.

Any help with this would be greatly appreciated!