Creating PivotTable From Scratch

StrangeLuck

New Member
Joined
Mar 25, 2002
Messages
15
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top