Run-time Error 1004 - But everything is the same!

[The_Woj]

Hello everyone! Long time reader first time poster here...

I have a macro that pulls data from a table into a pivot table and then sorts it. I use the same process/code 4 times so that I can break up the data into 4 separate pivot tables.

The code I use for the first 2 pivot tables works fine but for the third, I get the run time error 1004 "Unable to get the PivotFields property of the PivotTable class". I've compared the code and everything is seemingly the same. I'm at a loss for why I am getting this error.

Thank you in advance for your assistance!

In this example, everything works as planned:

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "D - RCO ATT_DID"
Sheets.Add After:=ActiveSheet
Sheets.Add After:=ActiveSheet
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "PT1"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "BHT1"
Sheets("rec").Select
ActiveSheet.Range("$A:$L").AutoFilter Field:=8, Criteria1:="=701", _
Operator:=xlOr, Criteria2:="=706"
Range("A:A,C:C,E:E").Select
Range("E1").Activate
Selection.Copy
Sheets("D - RCO ATT_DID").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CCS"
Range("E1").Select
ActiveCell.FormulaR1C1 = "CALL COUNT"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/100"
Range("E2").Select
ActiveCell.FormulaR1C1 = "1"
Range("D2:E2").Select
Selection.NumberFormat = "0.00"
Range("E2").Select
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"
Selection.AutoFill Destination:=Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
Range("E:E").Select
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)
Range("D:D").Select
Columns("A:E").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$E"), , xlYes).Name = _
"Table1"
Range("F1").Select
Sheets("PT1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"TABLE1", Version:=xlPivotTableVersion15).CreatePivotTable TableDestination _
:="PT1!R1C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion15
Sheets("PT1").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DATE")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("START HOUR")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("CALL COUNT"), "Count of CALL COUNT", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("SECONDS"), "Count of SECONDS", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("CCS"), "Count of CCS", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of CALL COUNT")
.Caption = "Sum of CALL COUNT"
.Function = xlSum
End With


However, in this example, I get the run-time error (where text is in red).

Sheets("Sheet7").Select
Sheets("Sheet7").Name = "F - STP ATT_DID"
Sheets("Sheet8").Select
Sheets("Sheet8").Name = "PT3"
Sheets("Sheet9").Select
Sheets("Sheet9").Name = "BHT3"
Sheets("rec").Select
Range("A1").Select
ActiveSheet.ShowAllData
ActiveSheet.Range("A:L").AutoFilter Field:=8, Criteria1:=Array("701", "703", "705"), Operator:=xlFilterValues
ActiveSheet.Range("A:L").AutoFilter Field:=11, Criteria1:="spf"
Range("A:A,C:C,E:E").Select
Range("E1").Activate
Selection.Copy
Sheets("F - STP ATT_DID").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CCS"
Range("E1").Select
ActiveCell.FormulaR1C1 = "CALL COUNT"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/100"
Range("E2").Select
ActiveCell.FormulaR1C1 = "1"
Range("D2:E2").Select
Selection.NumberFormat = "0.00"
Range("E2").Select
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"
Selection.AutoFill Destination:=Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
Range("E:E").Select
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)
Range("D:D").Select
Columns("A:E").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$E"), , xlYes).Name = _
"Table3"
Range("F1").Select
Sheets("PT3").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"TABLE3", Version:=xlPivotTableVersion15).CreatePivotTable TableDestination _
:="PT3!R1C1", TableName:="PivotTable3", DefaultVersion:= _
xlPivotTableVersion15
Sheets("PT3").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("DATE")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("START HOUR")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("CALL COUNT"), "Count of CALL COUNT", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("SECONDS"), "Count of SECONDS", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("CCS"), "Count of CCS", xlCount
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of CALL COUNT")
.Caption = "Sum of CALL COUNT"
.Function = xlSum
End With


The table that is sourcing the PivotTable has 5 columns (A:E). However, once the macro stops running due to the error, the table has incorrect labels and I do not know why. D1 should be "CCS" but instead it says "#VALUE!". E1 should be "CALL COUNT" but instead it says "1".

 

[Norie]

What's actually in D1?

Is it a formula or just #VALUE!?

Does this work any better?

Sheets("Sheet7").Name = "F - STP ATT_DID"

    Sheets("Sheet8").Name = "PT3"

    Sheets("Sheet9").Name = "BHT3"
    
    With Sheets("rec")
        .Range("A:L").AutoFilter Field:=8, Criteria1:=Array("701", "703", "705"), Operator:=xlFilterValues
        .Range("A:L").AutoFilter Field:=11, Criteria1:="spf"
        .Range("A:A,C:C,E:E").copy Sheets("F - STP ATT_DID").Range("A1")
    End With

    With Sheets("F - STP ATT_DID")
        .Range("D1").Value = "CCS"
        .Range("E1").Value = "CALL COUNT"
        .Range("D2").FormulaR1C1 = "=RC[-1]/100"
        .Range("E2").Value = "1"
        .Range("D2").NumberFormat = "0.00"
        .Range("E2").NumberFormat = "0"
        .Range("E2").AutoFill Destination:=Range("E2:E" & .Range("A" & Rows.Count).End(xlUp).Row)
        .Range("D2").AutoFill Destination:=Range("D2:D" & .Range("A" & Rows.Count).End(xlUp).Row)
        .ListObjects.Add(xlSrcRange, Range("$A:$E"), , xlYes).Name = _
        "Table3"


    End With

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                                      "TABLE3", Version:=xlPivotTableVersion15).CreatePivotTable TableDestination _
                                      :="PT3!R1C1", TableName:="PivotTable3", DefaultVersion:= _
                                      xlPivotTableVersion15
    With Sheets("PT3")

        With .PivotTables("PivotTable3").PivotFields("DATE")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotTables("PivotTable3").PivotFields("START HOUR")
            .Orientation = xlRowField
            .Position = 2
        End With
        .PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
                                                 "PivotTable3").PivotFields("CALL COUNT"), "Count of CALL COUNT", xlCount
        .PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
                                                 "PivotTable3").PivotFields("SECONDS"), "Count of SECONDS", xlCount
        .PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
                                                 "PivotTable3").PivotFields("CCS"), "Count of CCS", xlCount
        With .PivotTables("PivotTable3").PivotFields("Count of CALL COUNT")
            .Caption = "Sum of CALL COUNT"
            .Function = xlSum
        End With

    End With

 

[The_Woj]

Thank you so much for your quick reply! Unfortunately, I still get the same run-time error in the same location.

In response to your questions...

What's actually in D1? Is it a formula or just #VALUE!?

It's just #VALUE!. Cell D1 and E1 are supposed to be table headers (CCS and CALL COUNT, respectively). Cell D2 is supposed to be a formula (=C2/100) and cell E2 is supposed to be the number 1. No formula, just "1".

 

[The_Woj]

I feel quite silly now...but must confess my mistake! I was reviewing the code and noticed that one of the filters applied was for data that it shouldn't have been filtering by. I went back and checked my notes and realized that the raw data was incorrect.

I needed data from a city in Florida that begins with an "S" but instead pulled data from a city in California that also begins with an "S". Because the macro was trying to sort by data that did not exist in the California data, I received the error. Here's where I noticed the mistake ("705" was not in the raw data I had, and therefore caused the error. Thankfully there was no 705 otherwise I would have never realized that I was using raw data from the wrong site):

ActiveSheet.Range("A:L").AutoFilter Field:=8, Criteria1:=Array("701", "703", "705"), Operator:=xlFilterValues
    ActiveSheet.Range("A:L").AutoFilter Field:=11, Criteria1:="spf"

Nevertheless, I will be using your reformatted code as it is much cleaner than mine. I am a true beginner at this and appreciate you taking the time to help me along and offer up something better.

Thank you so much for your quick reply! Unfortunately, I still get the same run-time error in the same location.

In response to your questions...

What's actually in D1? Is it a formula or just #VALUE!?

It's just #VALUE!. Cell D1 and E1 are supposed to be table headers (CCS and CALL COUNT, respectively). Cell D2 is supposed to be a formula (=C2/100) and cell E2 is supposed to be the number 1. No formula, just "1".

 

[Norie]

No problem - think we've all been there.

 

[Rhodie72]

Here is your code in a more simplified way. As you go on learning, you may find little tips that speed up your code.

Don't forget to use CODE tags when posting. It makes it easier for us to decipher it and then it looks like this:

'   Rename sheets
    Sheet7.Name = "F - STP ATT_DID"
    Sheet8.Name = "PT3"
    Sheet9.Name = "BHT3"

    Sheets("rec").Range("A1").Activate
    With ActiveSheet
        .ShowAllData
        With .Range("A:L")
            .AutoFilter Field:=8, Criteria1:=Array("701", "703", "705"), Operator:=xlFilterValues
            .AutoFilter Field:=11, Criteria1:="spf"
        End With
    End With

    'Range("A:A,C:C,E:E").Select '  Redundant
    Range("E1").Copy _
    Sheets("F - STP ATT_DID") '.Select  '  Redundant _
    ActiveSheet.Paste '  Redundant
    Range("D1").FormulaR1C1 = "CCS"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "CALL COUNT"
    With Range("D2")
        .FormulaR1C1 = "=RC[-1]/100"
        .NumberFormat = "0.00"
    End With
    With Range("E2")
        .FormulaR1C1 = "1"
        '.NumberFormat = "0.0" '  Redundant
        .NumberFormat = "0"
        .AutoFill Destination:=Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
    End With
    'Range("E:E").Select '  Redundant
    Range("D2").AutoFill Destination:=Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)
    'Range("D:D").Select '  Redundant
    Columns("A:E").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$E"), , xlYes).Name = "Table3"
    'Range("F1").Select '  Redundant
    Sheets("PT3").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="TABLE3", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="PT3!R1C1", _
    TableName:="PivotTable3", _
    DefaultVersion:=xlPivotTableVersion15
    'Sheets("PT3").Select '  Redundant
    Cells(1, 1).Select
'   Simplified the functions to be nested and improve performance
    With Sheets("PT3").PivotTables("PivotTable3")

        With .PivotFields("DATE")
            .Orientation = xlRowField
            .Position = 1
        End With

        With .PivotFields("START HOUR")
            .Orientation = xlRowField
            .Position = 2
        End With

        With .PivotFields("Count of CALL COUNT")
            .Caption = "Sum of CALL COUNT"
            .Function = xlSum
        End With

        .AddDataField .PivotFields("CALL COUNT"), "Count of CALL COUNT", xlCount
        .AddDataField .PivotFields("SECONDS"), "Count of SECONDS", xlCount
        .AddDataField .PivotFields("CCS"), "Count of CCS", xlCount
    End With

I've not edited the code to correct it as it seems you now have a solution. With what is posted above you might find it's quicker to find the errors too!