VBA Creates Pivot Table Except when Row 1 is blank

[nirvehex]

Hello,

I have a code which creates a pivot table and works fine. Unless Row 1 is blank. Ideally, I need row 1 blank because I have a button there that brings you to a different tab.

I have created pivot tables from source data that is not at the top row. I think my code needs to start in row 2 for selecting the pivot source data. I'm just not sure how to make it do that.

Any idea why this is happening and how to fix?

Thanks.

Code:

Sub MakePivotTable()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("WorkOrders")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Pivot")
Dim PTCache As PivotCache
Dim PRange As Range
' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="SSPivot")
' Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True
' Set up the row fields
With pt.PivotFields("Material")
.Orientation = xlRowField
.Position = 1
End With
With pt.PivotFields("Equipment")
.Orientation = xlRowField
.Position = 2
End With
' Set up the data fields
With pt.PivotFields("Service On")
.Orientation = xlDataField
.Function = xlMax
.Position = 1
End With
With pt.PivotFields("Service On")
.Orientation = xlDataField
.Function = xlMin
.Position = 2
End With
With pt.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlCount
.Position = 3
End With
With pt.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlSum
.Position = 4
End With
With pt.PivotFields("Cost")
.Orientation = xlDataField
.Function = xlSum
.Position = 5
End With
With pt.PivotFields("Data")
.Orientation = xlColumnField
End With
' Now calc the pivot table
pt.ManualUpdate = False
End Sub

[Bob Phillips]

Sub MakePivotTable()
    Dim pt As PivotTable
    Dim strField As String
    Dim WSD As Worksheet
    Set WSD = Worksheets("WorkOrders")
    Dim PTOutput As Worksheet
    Set PTOutput = Worksheets("Pivot")
    Dim PTCache As PivotCache
    Dim PRange As Range
     ' Find the last row with data
    Dim finalRow As Long
    finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
     ' Find the last column with data
    Dim finalCol As Long
    finalCol = WSD.Cells(2, Application.Columns.Count).End(xlToLeft).Column
     ' Find the range of the data
    Set PRange = WSD.Cells(2, 1).Resize(finalRow - 1, finalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
     ' Create the pivot table
    Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
    TableName:="SSPivot")
     ' Define the layout of the pivot table
     ' Set update to manual to avoid recomputation while laying out
    pt.ManualUpdate = True
     ' Set up the row fields
    With pt.PivotFields("Material")
        .Orientation = xlRowField
        .Position = 1
    End With
    With pt.PivotFields("Equipment")
        .Orientation = xlRowField
        .Position = 2
    End With
     ' Set up the data fields
    With pt.PivotFields("Service On")
        .Orientation = xlDataField
        .Function = xlMax
        .Position = 1
    End With
    With pt.PivotFields("Service On")
        .Orientation = xlDataField
        .Function = xlMin
        .Position = 2
    End With
    With pt.PivotFields("Quantity")
        .Orientation = xlDataField
        .Function = xlCount
        .Position = 3
    End With
    With pt.PivotFields("Quantity")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 4
    End With
    With pt.PivotFields("Cost")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 5
    End With
    With pt.PivotFields("Data")
        .Orientation = xlColumnField
    End With
     ' Now calc the pivot table
    pt.ManualUpdate = False
End Sub

[nirvehex]

Thank you. I copied and pasted your code. Still, I get a Application-defined or object-defined error. It debugs to this line in my code:

 Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
    TableName:="SSPivot")

Any ideas?

[nirvehex]

It seems to be only "Grabbing" the first column as the pivot source and then stops.

[Bob Phillips]

Worked fine in my test, albeit limited data, but all columns.

[nirvehex]

What the heck! Now it works! LOL VBA is fun!

Thank you very much!