Get type mismatch error when trying to create pivot table

Hi all,

Background:
Using excel 2007.
Data file has ~ 85,000 rows of data.
Data file has ~ 20 columns of data.

I am getting a type mismatch error when i try to create the pivot table using the following vba code:

Please Login or Register  to view this content.

i thought this was strange to get a failure here because i had used this code before in other files.

The file size was rather large and was taking awhile to finish so i decided to reduce the input file size just so i could troubleshoot file quicker.

Turns out..............i dont get the error when i reduce the number of rows from ~85,000 to 53000. Kept number of cols the same....

So the code shown above works but not when the number of rows is ~ 85,000...........

Any one have an idea whats going on?

here is the code in that procedure.............

note: if its crucial that you have the workbook as well i may be able to do this but its going to take me some time to alter the data so that i can post it.

note: i am posting code in this enclosed excel file......i tried to post it directly but had some issue...so decided to post to file.

norie,

when i reduce the number of rows .....macro runs just fine.

its when the input file has ~ 85k rows that i have a rpoblem. I am searching other threads and from what i have found thus far there may be a limitation using my create pivot method............not sure though

i can but not sure how this helps............its going to take me a little while to alter the data file to remove sensitive information.

hi norie,
but i thought i identified the problem............its the number of rows?

norie, no this is not solved.......

the problem as best i can tell is the number of rows............i dont know why and i dont have a soln to the problem. i was hoping you would or at least point me in the right direction.......

i am enclosing the input test file per your request.

ok.....found this info from another site

HTML Code: 

i tried what this site suggested and it worked........no mimatch runtime error

To get it to work i did the following:
Removed this code:

Please Login or Register  to view this content.

replaced above code with this code:

Please Login or Register  to view this content.

Now the author of this post said the following:
'***
The limit occurs when trying to pass a Range object with greater than 65536 rows (max number of rows for xl2003), even if you are using xl2007 or xl2010.
The SourceData argument, which is a Variant type, handles either Range objects or Strings representing Range Address references below this limit.
For Ranges greater than 65536 rows, it appears to only work with Address References and not Range Objects.
A workaround for your code would be to convert the Range object into an Address reference.
'***

i dont understand this.......can someone help explain this to me?

norie,

i dont understand why my approach does not work and i dont understand why what they did does work.............

when i use the range when there are too many rows ......... i get the type mismatch error.

when i use the address it works in all cases..........

does anyone know why?