Too many rows in Pivot
 

Not a solution.. but

Your working with quite a bit of data (11000 rows / 27 columns)

Why not experiment (manually) with making the data into an olapcube or
basing your Pivot on a query rather then an Excel Range.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"ten" wrote:

Can Anyone please help me:

I have some data I want to put into an PivotTable (doing
some VBA-programming). That is ok.
However, I get a limitation for how many rows I can have:
I have now 5, but want 2 more. But when adding these
(either manually or through change in code), I get a
message telling me I have too many rows or coloumns.

Are there any way I can go past this issue?

I might add that the rows I want, are one to one to other
rows, so that I need no more sums. (Each Account has one
and only one AccountNO, Each Property has one and only one
PropertyNO).

My code follows, and I typically want to add more in
the "AddFields RowFields-section", which I want something
like:
AddFields RowFields:=Array("Property", _
"PropertyNO", "Company", "Acc.kl", "Acc.gr", _
"AccountNO", "Account"),

This is my code:

ActiveSheet.PivotTableWizard SourceType:=xlDatabase,
SourceData:= _
"'Lim inn Grunnlagsdata her!'!R1C1:R10598C27",
TableDestination:="", _
TableName:="Pivottabell1"
ActiveSheet.PivotTables("Pivottabell1").PivotField s
("PropertyNO").Subtotals = _
Array(False, False, False, False, False, False,
False, False, False, False, False, False)
ActiveSheet.PivotTables("Pivottabell1").PivotField s
("AccountNO").Subtotals = _
Array(False, False, False, False, False, False,
False, False, False, False, False, False)
ActiveSheet.PivotTables("Pivottabell1").PivotField s
("Account").Subtotals = _
Array(False, False, False, False, False, False,
False, False, False, False, False, False)
ActiveSheet.PivotTables("Pivottabell1").AddFields
RowFields:=Array("Property", _
"PropertyNO", "Acc.kl", "Acc.gr", "AccountNO"),
ColumnFields:="ColumnName", _
PageFields:="Portfolio"
With ActiveSheet.PivotTables
("Pivottabell1").PivotFields("Amount")
.Orientation = xlDataField
.Name = "Summer Amount"
.Function = xlSum
End With

Please advice! Thx!