Pivot table macro (Excel 2000)

Topic

New Reply

I want to write a macro for my pivot table. However, the amount of data (both columns and rows) changes each month. How do I set up a macro that will include the whole data base all the time, every time?

Reply | Quote

Replies

Use PivotTableWizard Method (see the VB help for additional info)
Use something like:

ActiveSheet.PivotTableWizard xlDatabase, Range(range(“a1”).CurrentRegion.Address)

It will create a new PivotTable from a Microsoft Excel database contained in the range defined by the current region of the activesheet starting from A1.

Steve

Reply | Quote

You can also name the region and update your table using the named region by refreshing it.

Here’s an example where I named my pivot table “Regional Summary” and I named the database “PivotDataBase”. Each time there is a change to the data, you could simply click a macro button that runs the following code:

With ActiveSheet.PivotTables(“Regional Summary”)
.PivotTableWizard SourceType:=xlDatabase, SourceData:=”PivotDataBase”
.RefreshTable
End With

This will update your pivot table with the new data.

Hope this helps.

Reply | Quote

You are brilliant. I like this idea. Thank you. I will be back to you soon on more help.

Reply | Quote

Well thank you for the kind words. Of the many things I am called, “brilliant” is not often among them. But feel free to pick my brain on pivot tables. As it happens, I do a lot of work with very complex pivot tables for a lengthy project I have been involved with.

Reply | Quote

Now I am ready for help. Do you name the pivot table in advance of the macro or use your code to name the pivot table during the macro? If you name it in advance, what are the commands to name the pivot table? Thank you.

Reply | Quote

What I did was name the pivot tables when I created them. You can do this by right-clicking anywhere within the pivot table, then selecting Table Option. The very first item in this dialog is the name. Just type in the name that you want and you can use that name in your macros. (If you don’t name your table, Excel simply gives it a generic name (PivotTable1, PivotTable2, and so on), and that can get very confusing if you deal with a lot of pivot tables (like I do).

FWIW, I have several Excel files that my users use as their pivot table templates. The files already contain various pivot tables based on sample data. That way all my macros have to do is update the existing tables with the new data (although that in itself has become quite complex, for a lot of different reasons).

When the file is opened, there is a blank worksheet named “Paste New Data Here”, and the user then pastes the new data into that sheet. Then the user selects the particular pivot table needed and clicks a macro button (each pivot table has its own macro button) that begins a series of complex actions, including copying the new data to a different worksheet (which I named “Pivot Data Base”), modifying it (adds columns and formulas) and naming it for further use with the macros. Each pivot table uses its own cache and has its own update macros, all of which call the macro that copies and modifies the database.

When the update macros complete execution, the pivot table is updated and formatted in accordance with the specifications they use here. Everything is automated so the user doesn’t have to do any actual work with the pivot table itself.

Regards,

Reply | Quote

Excellent! Your example is exactly what I am creating. Because the data changes monthly, I can not risk allowing the user to create the Pivot Table from scratch, and creating a mess. I must automate everything!

Reply | Quote

Please feel free to call on me anytime. I’ve been on this pivot table project since last November and I’ve learned A LOT about pivot tables and how they work.

Good luck…

Reply | Quote