none
"unable to get the pivotfields property of the pivot table class"

    Question

  • this macro works properly:

     With ActiveSheet.PivotTables("" & tblName & "").PivotFields("Freq")
            .Orientation = xlColumnField
            .Position = 1
     End With

    Because the ColumnFields can change based on user selection (the selected value is on Worksheets("Hidden").Cells(1, "e")), so i replace the PivotField value. The code become:

    With ActiveSheet.PivotTables("" & tblName & "").PivotFields(" " & Worksheets("Hidden").Cells(1, "e") & " ")
            .Orientation = xlColumnField
            .Position = 1
     End With

    the macro above generate Run-time error '1004':Unable to get the PivotFields property of the PivotTable class.

    i already checked with Msgbox what value return when i refer to Worksheets("Hidden").Cells(1, "e"), and it return the right field name, that is Freq.

    what would be the problem?

     

     

     

    Thursday, March 16, 2006 4:50 PM

Answers

  • I solved it already.

    xax = Trim(Worksheets("Hidden").Cells(1, "e"))

     With ActiveSheet.PivotTables(tblName).PivotFields(xax)
            .Orientation = xlRowField
            .Position = 1
        End With

    the problem is : in previous code, there are unexpected space before and  after the string saved in  Worksheets("Hidden").Cells(1, "e"). so we need to 'trim' it first, then everything will back to normal.

    Monday, March 20, 2006 12:55 PM

All replies

  • I solved it already.

    xax = Trim(Worksheets("Hidden").Cells(1, "e"))

     With ActiveSheet.PivotTables(tblName).PivotFields(xax)
            .Orientation = xlRowField
            .Position = 1
        End With

    the problem is : in previous code, there are unexpected space before and  after the string saved in  Worksheets("Hidden").Cells(1, "e"). so we need to 'trim' it first, then everything will back to normal.

    Monday, March 20, 2006 12:55 PM
  • I don't know if the spaces are the issue. The problem is more likely that the user did not reference the text items properly, and uses lots of double double-quotes unnecessarily. This line:

    With ActiveSheet.PivotTables("" & tblName & "").PivotFields(" " & Worksheets("Hidden").Cells(1, "e") & " ")

    should be cleaned up to yield this (assuming tblName is a string):

    With ActiveSheet.PivotTables(tblName).PivotFields(Worksheets("Hidden").Cells(1, "e").Value)

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

     

    Saturday, April 01, 2006 9:49 PM