MACRO PROBLEM: How to adjust a recorded pivot table to capture varying rows/Report Filter

[cgeorge4]

Using Excel 2007 - Windows 7

I need to adjust my recorded PT macro to capture all rows

1) Pivot Table should be on the same sheet as the Data
2) Data is in Column A thru P.
3) Data range has varying number of rows from month-to-month (300 to approx. 5000)
4) Pivot Table should be on column "S24"
5) This sheet cannot be turned into a table (because my boss doesn't like tables)


Note: "Use Relative References" was turned on before I recorded the steps. Here is the recorded code without any changes by me.

Option Explicit

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _        "ACNOF!R1C2:R1048576C16", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="ACNOF!R24C20", TableName:="PivotTable9", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("ACNOF").Select
    Cells(24, 20).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Site Code")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Site Code"), "Count of Site Code", xlCount
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant")
        .PivotItems("Vacant").Visible = False
        .PivotItems("(blank)").Visible = False
        .PivotItems("Transfer").Visible = False
        .PivotItems("(blank)").Visible = False    
End With
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable9").TableStyle2 = "PivotStyleLight1"
End Sub

Below is the code shown above, but with changes made by me in an attempt to make it capture all number of rows:
The two rows with red text are the only rows that I changed.

The error I get is "Variable Not Defined" and the first word "FinalRow" is highlighted blue.


IMPORTANT NOTE: The listed items in the pivot table's Report Filter will be different from month-to-month - but the column header will always be "Common vs. Vacant". I need one pivot table per Report filter item.

The pivot tables created will always be two columns wide - so they can be on the same row - but have one blank column between them.

If possible (and only because I think it would look better) , I would prefer that each pivot table is created one below the other - with perhaps 4 rows between.

[B][COLOR=#a52a2a]FinalRow = Cells(Rows.Count, 1).End(xlUp).Row[/COLOR][/B]    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "[B][COLOR=#a52a2a]ACNOF!R1C2:R" & FinalRow & "C16",[/COLOR][/B] Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="ACNOF!R23C19", TableName:="PivotTable7", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("ACNOF").Select
    Cells(23, 19).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Site Code")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
        "PivotTable7").PivotFields("Site Code"), "Count of Site Code", xlCount
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Common vs Vacant")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("PivotTable7").TableStyle2 = "PivotStyleLight2"
End SubFinalRow = Cells(Rows.Count, 1).End(xlUp).Row    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ACNOF!R1C2:R" & FinalRow & "C16", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="ACNOF!R24C20", TableName:="PivotTable9", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("ACNOF").Select
    Cells(24, 20).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Site Code")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Site Code"), "Count of Site Code", xlCount
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant")
        .PivotItems("Vacant").Visible = False
        .PivotItems("Off Site").Visible = False
        .PivotItems("Transfer").Visible = False
        .PivotItems("(blank)").Visible = False
   

    End With
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable9").TableStyle2 = "PivotStyleLight1"
End Sub

Darlings, Thank you in advance for your assistance because this is beyond me.
Juicy,

 

[apo]

Hi..

Try changing the block of code that creates the Pivot Table to something like this..

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
   'Sheets("ACNOF").Range("A1:P" & FinalRow)).CreatePivotTable _
   '     TableDestination:=Sheets("ACNOF").Cells(24,19),TableName:="PivotTable7"

I removed this part..

[COLOR=#333333] DefaultVersion _
[/COLOR][COLOR=#333333]        :=xlPivotTableVersion12[/COLOR]

If you include that.. your Pivot table will probably have issues if you run the Workbook on a different version of Excel (other than Excel 2007).

Also.. don't forget to add..

Dim FinalRow as Long

 

[cgeorge4]

Hi, thanks for responding so quickly. I used your first and second suggestion and the code failed - but the 3rd one worked.

I added "Dim FinalRow as Long" and it did create a pivot table, which is great!

The problem now is that the pivot table is created with out anything for "Row Fields".
The error I received is "Unable to get the pivot table properties of the property field class"

Here is my code as it is now (I added screen updating)

Dim FinalRow As LongApplication.ScreenUpdating = False
With ActiveSheet


FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ACNOF!R1C2:R" & FinalRow & "C16", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="ACNOF!R23C19", TableName:="PivotTable9", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("ACNOF").Select
    Cells(24, 20).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Site Code")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Site Code"), "Count of Site Code", xlCount
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant")
        .PivotItems("Vacant").Visible = False
        .PivotItems("Occupied").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Common vs Vacant"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable9").TableStyle2 = "PivotStyleLight1"
    
    End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thanks,