Creating Pivot Tables

[ribhead]

O.K. I am a super newby when it comes to VBA and Excel so pardon the ignorance. Here is my question. I am creating a database and whenever an operator clicks a button a line is inserted and information gets put in that line via userforms. I want to create a pivot table to store all the data. What is the easiest way to do this? I've tackled userforms and this seems pretty intense so I think I'll need a bit of help. P.S. Any idea how many lines of information can be stored on an Excel spreadsheet before memory becomes a problem?

I may not be very smart but I'm sure wirey!!!!

 

[xlbo]

Easiest way (ie laziest and requires least updating) would be to use a dynamic named range (enter the following formula into the Insert>Name>Define box - compensating for sheet names)
=OFFSET(Sheetname!$A$1,0,0,COUNTA(Sheetname!$A:$A),COUNTA(Sheetname!$1:$1))
Lets say you call it "MyPvtRng"
Then base the pivot table data area as that range name ie in the data range box in the pivot table wizard, enter
=MyPvtRng

This should expand as the data does and the pivot table, when refreshed, should show this


Point 2
Excel has a max of 65536 rows per sheet but I would suggest that anything over 30000 lines is gonna make it run like a dog

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes