VBA Pivot Table Run-time Error '1004'

mesmanc

New Member
Joined
Apr 28, 2014
Messages
4
Greetings,

I am currently experiencing the following error: Run-time error '1004': Unable to get the PivotFields property of the PivotTable class on this line of code: With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Project")

I can change "Count of Project" to "Project" and i get a different error: Run-time error '1004': Unable to set the Position property of the PivotField class with .Position = 2 highlighted.

I am trying to run 3 different pivot tables within 1 new sheet. The first table populates without any issue, but the second table only populates the first "Projects" column and not the "Count of Projects"

Thanks for the help!

Code:
    Rows("1:3").Select    Selection.Delete Shift:=xlUp
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    DataSheet = ActiveSheet.Name
    Sheets.Add
    NewSheet = ActiveSheet.Name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        DataSheet & "!R1C1:R" & FinalRow & "C14", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:=NewSheet & "!R3C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets(NewSheet).Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Assignee")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Key")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Key"), "Count of Key", xlCount
    ActiveCell.Offset(0, 3).Range("A1").Select
    Sheets(NewSheet).Select
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        DataSheet & "!R1C1:R" & FinalRow & "C14", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:=NewSheet & "!R3C4", TableName:="PivotTable2" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets(NewSheet).Select
    Cells(3, 4).Select
     With ActiveSheet.PivotTables("PivotTable2").PivotFields("Project")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Project")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Project"), "Count of Project", xlCount
    ActiveCell.Offset(0, 5).Range("A1").Select
    Sheets(NewSheet).Select
    ActiveWorkbook.Worksheets(NewSheet).PivotTables("PivotTable2").PivotCache. _
        CreatePivotTable TableDestination:=NewSheet & "!R3C7", TableName:="PivotTable3" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets(NewSheet).Select
    Cells(3, 7).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Priority")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Key")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Key"), "Count of Key", xlCount
    ActiveWindow.SmallScroll Down:=-12
    ActiveCell.Offset(-1, -6).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Overdue JIRAs By Employee"
    ActiveCell.Offset(0, 3).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Overdue JIRAs by Client"
    ActiveCell.Offset(0, 3).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Overdue JIRAs by Priority"
    ActiveCell.Offset(0, -6).Range("A1").Select
    Selection.Font.Bold = True
    ActiveCell.Offset(0, 3).Range("A1").Select
    Selection.Font.Bold = True
    ActiveCell.Offset(0, 3).Range("A1").Select
    Selection.Font.Bold = True
    ActiveCell.Offset(9, 5).Range("A1").Select
    ActiveWindow.SmallScroll Down:=-36
    ActiveCell.Offset(-9, -11).Range("A1").Select
    Selection.Font.Size = 12
    ActiveCell.Offset(0, 3).Range("A1").Select
    Selection.Font.Size = 12
    ActiveCell.Offset(0, 3).Range("A1").Select
    Selection.Font.Size = 12
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Can you please expand on that with an example? I am not sure exactly what the "row area" is.

Thank you!
 
Upvote 0
I ended up using a different PivotField and it worked. Luckily the data that I am using has two columns that are essentially the same information.

Thanks for the help!
 
Upvote 0
Can you please expand on that with an example? I am not sure exactly what the "row area" is.

Thank you!

You had:

Rich (BB code):
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Project")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Project")
        .Orientation = xlRowField
        .Position = 2
    End With
 
Upvote 0
I get the Run-Time error '1004' when I run the following code and I cannot seem to figure out what is wrong:

Sub MarketMacro()
'
' MarketMacro Macro
'

'
Sheets("Pivot").Visible = True
Sheets("Pivot").Select
ActiveSheet.PivotTables("MarketPivot").PivotFields("State").CurrentPage = Range("E2").Text
ActiveSheet.PivotTables("CostCenterPivot").PivotFields("State").CurrentPage = Range("E2").Text
ActiveSheet.PivotTables("ProcessLevelPivot").PivotFields("State").CurrentPage = Range("E2").Text
ActiveSheet.PivotTables("ClinicPivot").PivotFields("State").CurrentPage = Range("E2").Text
Range("I1").Select
ActiveCell.FormulaR1C1 = "0"
Sheets("Position Request").Select
Sheets("Pivot").Visible = False

End Sub

Each month the list of cost centers and markets is updated and the code has been working just fine until this month.

Help!!! :)
 
Upvote 0
the line of code showing the error is
ActiveSheet.PivotTables("CostCenterPivot").PivotFields("State").CurrentPage = Range("E2").Text
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top