Creating PivotTable From Scratch
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Creating PivotTable From Scratch

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?



  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  6. #6
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi

    Try here, many examples.

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com