Pivot Table and Macro. Row disapear.

[jbesclapez]

Hi Mr Excel!

I have a macro that i recorded to do a pivot table.
When i run it, it doesnot put the rows. I dont understand because when i record i do put them.

Here is the code below :

Sub Macro9()
Worksheets.Add().Name = "DynTab"
'Create a dynamic table on the DynTab sheet create before in Cell A4

    Sheets("Attachments-PO").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Attachments-PO!R1C1:R95C6", Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="DynTab!R4C1", TableName:="PivotTable5" _
        , DefaultVersion:=xlPivotTableVersion10
    Sheets("DynTab").Select
    Cells(4, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("PO")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("PO"), "Count of PO", xlCount
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("UnitCC")
        .Orientation = xlPageField
        .Position = 1
    End With
    Columns("A:A").ColumnWidth = 24.14
    Cells.Select
    Selection.RowHeight = 13.5
    Range("C1").Select

End Sub

Thanks for your help

 

[Andrew Poulsom]

I think that the existing PO field is being moved from the row area to the data area. Changing the order of field creation worked for me:

Sub Macro9()
Worksheets.Add().Name = "DynTab"
'Create a dynamic table on the DynTab sheet create before in Cell A4
    Sheets("Attachments-PO").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Attachments-PO!R1C1:R95C6", Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="DynTab!R4C1", TableName:="PivotTable5" _
        , DefaultVersion:=xlPivotTableVersion10
    Sheets("DynTab").Select
    Cells(4, 1).Select
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("PO"), "Count of PO", xlCount
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("PO")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("UnitCC")
        .Orientation = xlPageField
        .Position = 1
    End With
    Columns("A:A").ColumnWidth = 24.14
    Cells.Select
    Selection.RowHeight = 13.5
    Range("C1").Select
End Sub

 

[jbesclapez]

Thanks again MrExcel, you got it right! I would not have find that alone!