Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot table macro (Excel 2000)

    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot table macro (Excel 2000)

    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

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table macro (Excel 2000)

    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.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table macro (Excel 2000)

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

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table macro (Excel 2000)

    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.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table macro (Excel 2000)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table macro (Excel 2000)

    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,

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table macro (Excel 2000)

    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!

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table macro (Excel 2000)

    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •