Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VBA Pivot Table Run-time Error '1004'

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Pivot Table Run-time Error '1004'

    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Pivot Table Run-time Error '1004'

    You can't have the same field in the row area more than once.
    Microsoft MVP - Excel

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Pivot Table Run-time Error '1004'

    Can you please expand on that with an example? I am not sure exactly what the "row area" is.

    Thank you!

  4. #4
    New Member
    Join Date
    Apr 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Pivot Table Run-time Error '1004'

    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!

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Pivot Table Run-time Error '1004'

    Quote Originally Posted by mesmanc View Post
    Can you please expand on that with an example? I am not sure exactly what the "row area" is.

    Thank you!
    You had:

    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
    Microsoft MVP - Excel

  6. #6
    New Member
    Join Date
    Aug 2016
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Pivot Table Run-time Error '1004'

    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!!!

  7. #7
    New Member
    Join Date
    Aug 2016
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Pivot Table Run-time Error '1004'

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •