Creating PivotTable From Scratch

[StrangeLuck]

Just wondering if anyone could point me in the right direction in order to create a PivotTable without using the Wizard? I want to create a macro that will create PivotTables from different daily sets of data. When I try to record a macro with the Wizard in it, it doesn't work properly. Any help would be greatly appreciated. Thanks everyone!

[Juan Pablo González]

Why doesn't it work "properly" ? I did a rather large project about 4 months ago to create Pivot Tables from scratch, and I got all the "right info" by creating a simple pivot table, and go from there... used Online help A LOT too !

Another thing that helped was to declare variables... for example

Dim PT as PivotTable

that gives you Instant access to all of its properties and methods.

[StrangeLuck]

When I say that the PivotTable macro won't run properly, I mean that if I create the macro with one set of data, it works fine. However, when I try to use the same macro on a different set of data, the PivotTable doesn't calculate the data properly. I think what I am asking for is a method whereby a macro can be built to create a PivotTable for dynamic ranges of data. Is that easier to understand?
Thanks!

[Dave Hawley]

Hi StrangeLuck

If you are not familiar with Excel VBA you have picked a real hard one to learn by. There is so many different Objects, Properties and Methods for Pivot Tables about the only way you can normally get the 'Start' of any code is by Recording a Macro.


Couldn't you have a pre-setup Pivot Table refering to a dynamic range and drop any new data into it?

[StrangeLuck]

Thanks for the replies. I guess my question would be now how do I set up a PivotTable for a dynamic range of data? How do I name a range of data anyhow?

[RichardS]

To name a range, highlight the range then click in the name box. This is the drop down box just above column A. Enter a name for the range. Alternatively use Insert menu, Name and define.
Now when you create the pivot table, ensure that the Range referred to in step 2 of the wizard is the named range, bot a fixed cell reference.
The biggest issue with this is when you add data, ensuring the named range extends. I use data form for this. If you're extracting data from an external source, you should be able to remove the old named range, then re-apply and upfate the pivot table.
Richard

[Dave Hawley]

Hi

Try here, many examples.

http://www.ozgrid.com/Excel/DynamicRanges.htm