VB and Pivot Tables

[velohead]

Hi All,

I'm using Excel 2003

I have a spreadsheet where I frequently....
delete a particular pivot table
then recreate the same pivot table.

Can I automate the pivot table creation using VB ?
The data is contiguous, and the pivot table is very simple..
columns = months
rows = descriptions
values = amounts

Thanks

 

[GlennUK]

Can I ask why you are deleting the PivotTable and then recreating it? What is the purpose behind doing that?

 

[velohead]

deleting and recreating the pivot because...

a) if any details are changed within the source data, such as changing a description or category, then when the pivot is refreshed these 'new' items then appear at the bottom.
I prefer alphabetical order, so i guess i would have to do some sort of data sort, which I have not tried to do as yet.

b) also, the source data area may get longer, as new months are appended to the data.


As the pivot is so simple, I thought this might be the way to go, depending on how easy the code may be, of course.

 

[GlennUK]

Use a dynamic name for the source ... then as new data is appended it will get picked up on refresh.

And then sort the pivottable. That is a one click operation.

 

[velohead]

Sounds good to me, and I'll try them.
But how do I do each, please.

Is setting up a dynamic name a bit like ODBC, or more like a named area ?

Is data sort one click, I can only see.. "move up / move down etc" under the Order option when Right Click.

NB - using Excel 2003

 

[GlennUK]

A dynamic name is a defined name that contains a formula to return a range. Do Insert/Name/Define, choose a name of PivotSource, and have a definition like:
=OFFSET($A$1,0,0,COUNTA($A:$A),12)

adjusting references and width (I've specified 12 columns ) to your case.

As for sorting, select a field cell, and press a Sort button ( if I remember rightly, you should have those on one of your main toolbars ).

 

[velohead]

Sort Data - wow, that works like an absolute dream.
Literally two mouse clicks, and the pivot is perfect (ie alpha order).
I did not know one could do that, in that way.
Thank You.

 

[velohead]

Dynamic Name - Think i am struggling on this.

I have created a 'named area' using..."Do Insert/Name/Define, choose a name of PivotSource, and have a definition like: =OFFSET($A$1,0,0,COUNTA($A:$A),12)"

But what next ?

One can see a 'named area' in the drop down field, just above column A or B.
But this 'PivotSource' is not in the drop down field, but it does exist, I can see it via Insert/Name/Define.

 

[GlennUK]

A dynamically defined range name does not appear in the names drop down ... that is expected. You specify the name as the input range for the PivotTable, that's what the name is for.

 

[velohead]

Thanks GlennUK, it is now working ok, in that the pivot is now created using PivotSource.

Should work a dream when I do the month-end stuff.
Thanks for your time on this.