pivot table VBA misery

[RET79]

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

 

[Damon Ostrander]

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

 

[RET79]

Damon,

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

RET79

 

[RET79]

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

 

[RET79]

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

 

[RET79]

OK my final call on this topic, anyone?

RET79