Error on Pivot Table VBA

[geng]

Hi, I got a sample VBA code form this forum, this is to insert Pivot Table, and I tried modifying it, but it just gave me an error.

Option Explicit 
Sub Pivot() 
     ' pivot Macro
    Dim pt As PivotTable 
    Dim strField As String 
    Dim WSD As Worksheet 
    Set WSD = Worksheets("Sheet1") 
    Dim PTOutput As Worksheet 
    Set PTOutput = Worksheets("Pivot Table") 
    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:="Pivot") 
     
     ' Set update to manual to avoid recomputation while laying out
    pt.ManualUpdate = True 
     
     ' Set up the row fields
    pt.AddFields RowFields:=Array( _ 
    "Market", "Date", "Category", "Business") 
     ' Set up the data fields
    With pt.PivotFields("Pieces Ordered") 
        .Orientation = xlDataField 
        .Function = xlSum 
        .Position = 1 
    End With 
    With pt.PivotFields("Actual Sales") 
        .Orientation = xlDataField 
        .Function = xlSum 
        .Position = 2 
    End With 
    With pt.PivotFields("Lost Sales") 
        .Orientation = xlDataField 
        .Function = xlSum 
        .Position = 3 
    End With 
     ' Now calc the pivot table
    pt.ManualUpdate = False 
End Sub

This line is giving me the error

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

Thanks!!

[romperstomper]

Your data starts in row 2 of your data sheet, not row 1, so you need to adjust two lines:

    finalCol = WSD.Cells(2, Application.Columns.Count).End(xlToLeft).Column
    
    ' Find the range of the data
    Set PRange = WSD.Cells(2, 1).Resize(finalRow, finalCol)

[geng]

Thanks romperstomper!

But another thing

I was trying to modify this line:

.Orientation = xlDataField

instead of xlDatafield I'm trying to change it to xlcolumnfield since I want to be "Pieces Ordered" to be a column header instead of being in a row. But it gives me an error on this line:
.Function = xlSum

Thanks for your time!

[romperstomper]

If it's not a data field, then you can't sum it - you just get one column for each value. Are you trying to align the datafields in columns rather than rows?

[geng]

Yes, I need to align it in Columns. Please see snapshot attached.

Thanks!

[romperstomper]

You basically need to add:

   pt.DataPivotField.Orientation = xlColumnField

after adding the data fields.

[geng]

Where will I add it?

In this?

With pt.PivotFields("Pieces Ordered") 
        .Orientation = xlDataField 
        .Function = xlSum 
        .Position = 1 
    End With

I tried doing it like this:

With pt.PivotFields("Pieces Ordered") 
 pt.DataPivotField.Orientation = xlColumnField
        .Orientation = xlDataField 
        .Function = xlSum 
        .Position = 1 
    End With

And like this:

With pt.PivotFields("Pieces Ordered") 
 pt.DataPivotField.Orientation = xlColumnField
        .Function = xlSum 
        .Position = 1 
    End With

But still gives me error and highlight the xlcolumnfield line

[romperstomper]

after adding the data fields.

(emphasis added)

Option Explicit 
Sub Pivot() 
     ' pivot Macro
    Dim pt As PivotTable 
    Dim strField As String 
    Dim WSD As Worksheet 
    Set WSD = Worksheets("Sheet1") 
    Dim PTOutput As Worksheet 
    Set PTOutput = Worksheets("Pivot Table") 
    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:="Pivot") 
     
     ' Set update to manual to avoid recomputation while laying out
    pt.ManualUpdate = True 
     
     ' Set up the row fields
    pt.AddFields RowFields:=Array( _ 
    "Market", "Date", "Category", "Business") 
     ' Set up the data fields
    With pt.PivotFields("Pieces Ordered") 
        .Orientation = xlDataField 
        .Function = xlSum 
        .Position = 1 
    End With 
    With pt.PivotFields("Actual Sales") 
        .Orientation = xlDataField 
        .Function = xlSum 
        .Position = 2 
    End With 
    With pt.PivotFields("Lost Sales") 
        .Orientation = xlDataField 
        .Function = xlSum 
        .Position = 3 
    End With
 pt.DataPivotField.Orientation = xlColumnField

     ' Now calc the pivot table
    pt.ManualUpdate = False 
End Sub

[geng]

Thanks romperstomper, I'll try the code then I'll let you know!

thanks!