Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: pivot table VBA misery

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there.

    Can anyone here explain to me why this code doesnt work??


    Set rngpivotcol = Range([A12], [A65536].End(xlUp))

    y = rngpivotcol.Cells.count

    ReDim pivotcol(1 To y, 1)

    pivotcol = rngpivotcol

    For i = 2 To y
    Debug.Print pivotcol(i, 1)

    ActiveSheet.PivotTables("PivotTable1").PivotFields(Array(i)).Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)


    Next i

    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=pivotcol


    It's this last line that doesn't work. I thought that RowFields liked arrays. Oh well, any help appreciated folks.

    RET79

    [ This Message was edited by: RET79 on 2002-04-18 16:56 ]

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi RET79,

    I believe that the problem is that when the helps say that the RowFields parameter will accept an array it really means that it will accept a Variant CONTAINING an array (a subtle distinction, I'll admit). I think it will work if you assign the array to a variant like this:

    Dim x As Variant
    x = pivotcol
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=x



    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Damon,

    I have tried this but it sadly didn't work !

    RET79

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Has anyone got any fresh ideas about this one?

    Also, I don't like the code I have written to say no to subtotals, i.e. the line:


    ActiveSheet.PivotTables("PivotTable1").PivotFields(Array(i)).Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)


    I wonder if anyone has a more elegant way of doing this.

    Many thanks,

    RET79

    [ This Message was edited by: RET79 on 2002-05-01 16:42 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, sorry about this but I am calling this message up one more time. Any ideas appreciated, otherwise its hard coding for me (sigh)

    RET79

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK my final call on this topic, anyone?

    RET79

Some videos you may like

User Tag List

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
  •