I am trying to work through this. The configuration I had for the pivot
completely changed. I can now exceed 65536 but Excel decided that my
format was not something that was important so it completely revamped things
into an unusable mess:)

in example instead of the data fields being rows them became columns, and
all of my identification information decided to combine into one column


Still working.

"Roger Govier" wrote:

Hi

your problem is that you wrote the code under XL2002.
You have obviously moved to XL2007 (xlsm file and 77,000 rows)

Change
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

to
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
--
Regards
Roger Govier

Epidemic wrote:
once my report exceeded 65536 this command no longer works yielding an error
type missmatch. the command worked fine until the size exceeded 65536 and I
know RWS variable is where I am having problems. But I do not know why or
how to overcome the problem. below you will find the code which is broken.




Workbooks(SSRname).PivotCaches.Create(SourceType:= xlDatabase,
SourceData:=SrcDat.Range(SrcDat.Cells(1, 1), SrcDat.Cells(RWS, CLMN)),
Version:=xlPivotTableVersion10).CreatePivotTable
TableDestination:=Workbooks(SSRname) .Worksheets(DTPivot).Cells(1, 1),
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10





I have broken out the specific problem area for you to see.


Specifically the SourceData:=SrcDat.Range(SrcDat.Cells(1, 1),
SrcDat.Cells(RWS, CLMN))

RWS = 75000
CLMN= 43

I have dimensioned RWS as long

.