I have a tool that is used but several cellular markets it involves thousands
of lines of code, and does more than just create pivots. it is not as
easy as start over again. hell I am not sure I could ever create something
as good as this tool again period. I simply need to know if there is a way
to improve the source data portion of the macro to include more than 65,536
records with as little modification as possible. my current range command
uses Cells (variable1, variable2) somehow the way I am using the range
command has a problem with more than 65536 records.

"ryguy7272" wrote:

It may be time to start learning MS Access:
http://www.mrexcel.com/tip102.shtml

If you don't have access to Access, look at this:
http://social.msdn.microsoft.com/For...b-89be1c2ecc97

Or, this:
http://www.pcreview.co.uk/forums/thread-3548035.php

Or, this:
http://accountant.intuit.com/practic...thquic kbooks



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"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