pivot table VBA misery
VBA Telemetry pings you when your VBA projects fail
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,240
    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

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
  •  

 

 
DMCA.com