Just turn on the macro recorder while you create the pivot table manually.
This will give you the code you need.

You can then modify the code to make the range for the pivot table dynamic -
although it might be easier to use a named range

insert - name - define

Name: Database
Refersto: =offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),3)

Then use this defined name as the source for you pivot Table.

--
Regards,
Tom Ogilvy

"David Black" wrote in message
...
Hi Tom,

Thanks for the respons. I really appreciate the help.
And it works fine too.
I just have one more question. Is there a way to let the end of the cell
range change dynamically ?

I'll try to explain what I mean:
I have made a userform so people can easily type in the chemical they
used, with the corresponding number and the amount they used.
These values are written to a table in a worksheet. So the end of the
table changes dynamically. Chemicals are added to the end of the table.
Then I made a userform that lets the people give in the year and quarter
for which they want to know the total amount of products used. (actually
the total stockvalue, but that's not important)
So this is where the array is created: the products with their
particular number and the amounts used (for a certain quarter of a
certain year) are saved in the array I posted in the first message.
This is all done through VBA. When the user clicks a button after giving
the year and quarter, I want him to see the total amount used and I want
this to be written to a spreadsheet.
Can I create a Pivottable with VBA ? So when the user clicks the button,
the Pivottable is automatically created.

Thanks a lot,

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!