+ Reply to Thread
Results 1 to 15 of 15

Creating multiple pivot tables using VBA

  1. #1
    Registered User
    Join Date
    03-05-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    30

    Creating multiple pivot tables using VBA

    Hi all - hope you can help me as my boss is killing me over this!

    I am having a lot of trouble with pivot tables in Excel 2007 VBA. I am trying to create pivot tables using macros (connected to buttons the user can press to create the pivot table) - please don't ask why, but i need to do this!!!

    I used the record fuction in excel 2007 to produce macro code which will produce the required pivot tables when the user presses a button.

    Unfortuanately the coding seems to work fine when i have one pivot table in a file but breaks down if i record code to produce another pivot table.

    I have attached some code below (which was produced by the record function) and is intended to produce 2 seperate pivot tables (the macro submacro2 produces the 1st pivot table and the macro submacro4 lower down the page produces the 2nd pivot table). I have also indicated the point in sub 4 where the code breaks down - basically submacro4 just doesnt run!

    I hope you guys can help me and suggest corrections to the code in sub4 so that I can produce the required 2nd pivot table (and perhaps more besides!)

    PLEASE HELP!!!

    Thanks,

    Sam
    Attached Files Attached Files
    Last edited by VBA Noob; 04-03-2009 at 01:16 PM.

  2. #2
    Forum Contributor
    Join Date
    09-18-2008
    Location
    india
    Posts
    158

    Re: Creating multiple pivot tables using VBA

    Hi,

    Try the attachment remove your comments and paste it and let me know where are you placing your cursor for next pivot.


    Regards,
    Shekar.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-05-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Creating multiple pivot tables using VBA

    Thanks for getting back to me Shekar - I'll have a look at that now and get back to you ASAP - Thanks,


    Sam

  4. #4
    Registered User
    Join Date
    03-05-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Creating multiple pivot tables using VBA

    Hi Shekar/all - I still haven't been able to solve the problem!...grrrrr

    I have attached a small toy file which helps to illustrate the problems i am having- it contains the relevant formatting/macros and data and produces the same error - if we can make this work we are in business

    On the cycling page the button produces the required pivot table perfectly, however, on the temporal performance page, the required pivot table is not produced and an error message is returned! - hope this makes things clear

    Thanks in advance,

    Sam
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    09-18-2008
    Location
    india
    Posts
    158

    Re: Creating multiple pivot tables using VBA

    Hi,

    Try this and please change the sheet name to "Temporal".


    Please Login or Register  to view this content.
    Regards,
    Shekar.

  6. #6
    Registered User
    Join Date
    03-05-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Creating multiple pivot tables using VBA

    Unfortunately this still didn't work - any more ideas? Thanks for your help-

    Did you get the toy file to work?

    Cheers,

    Sam

    p.s - it still flags an error in the following code section - could it be related to the fact that the pivot table draws in such a large data set?

    Please Login or Register  to view this content.
    Last edited by VBA Noob; 04-03-2009 at 01:17 PM.

  7. #7
    Registered User
    Join Date
    03-05-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Creating multiple pivot tables using VBA

    Hi guys - are we out of ideas on this? - thanks for the help anyway

    Sam

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,400

    Re: Creating multiple pivot tables using VBA

    This changes will create the 2 pivot tables.
    Not sure whether the formatting is exactly what you wanted.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    The codes will fail on second run unless the tables have previously been deleted.
    Cheers
    Andy
    www.andypope.info

  9. #9
    Registered User
    Join Date
    03-05-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Creating multiple pivot tables using VBA

    I'll give it a go Andy - thank you very much

    Sam

  10. #10
    Registered User
    Join Date
    03-05-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Creating multiple pivot tables using VBA

    Hi Andy-

    your code worked in the small test book, but i can't get it to work in my larger program - i get Runtime error '13' warnings when i try to run each macro
    - the errors both occur at the same point in the code - shown below:

    Please Login or Register  to view this content.
    Obviously the position of the data in the new sheet is slightly different, but i think i corrected for that.

    You seem to have a very good grasp of this (as usual you have put me to shame), and seem to be very close to the answer, so maybe i should tell you exactly what i have and am trying to do....

    I have attached a file showing the exact positions of the data i am trying to pivot table in my main program (see attachment - Positions of data )

    1) Cycling data in the range c4:f104828 on the "Cycling page"
    2) Temporal performance data in the range c5:f104828 on the "Temporal performance page" in my main program

    Obviously this is a large amount of data - i am producing a simulation of the operation of a fuel cell with data of house electrical and heating demands for every 5 minute period in a whole year!!!

    I need to produce the following pivot table reports (and layout of these pivot tables is shown in the 2nd attachment - Pivot tables required:

    TASK 1 - cycling pivot table - i need the summed results/# of cycles to be grouped into 7 day periods i.e. the y axis (date) should be grouped into 7 day groups

    TASK 2 - temporal performance pivot table - i need the summed results grouped into hours of the day and weeks of the year i.e the y axis (date) should be grouped into 7 day groups and the x axis (time) should be grouped into hour long groups - this allows me to show when the fuel cell saves money and/or carbon and illustrates when (in the year) the fuel cell should be turned on

    I no longer need to do any fill/colour formatting.

    I appreciate i am asking quite a lot, but any help you could provide would be greatly appreciated - maybe i can help you in return 1 day!!!

    Thanks again,

    Sam
    Attached Files Attached Files
    Last edited by VBA Noob; 04-03-2009 at 01:17 PM.

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,400

    Re: Creating multiple pivot tables using VBA

    You change the location of the data so you need to change the code.

    Please Login or Register  to view this content.
    If you set the reference to top left cell then the current region will pickup all the data. The code already places the table to the right of the data.

  12. #12
    Registered User
    Join Date
    03-05-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Creating multiple pivot tables using VBA

    Pretty sure i did this- will check and get back to you- thanks for your help

    Sam

  13. #13
    Registered User
    Join Date
    03-05-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Creating multiple pivot tables using VBA

    I have made the data position changes in the temporal performance pivot table macro but i still get the same Runtime error '13' warning in the following code fragment:

    Please Login or Register  to view this content.
    Really irritating as your code works perfectly in the smaller test book!!!! - bizarre! Why would it not work in the larger program? Can you think of any reasons for this/offer any suggestions?

    Sorry to keep bugging you! and thank you so much for giving up your time to help me

    Thanks again,

    Sam
    Last edited by VBA Noob; 04-03-2009 at 01:18 PM.

  14. #14
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,400

    Re: Creating multiple pivot tables using VBA

    I can't think of anything to cause that Type mismatch error.

    Do the variables reference valid ranges?

  15. #15
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Creating multiple pivot tables using VBA

    Really irritating as your (Andy's) code works perfectly in the smaller test book!!!! - bizarre! Why would it not work in the larger program?
    These are longshot questions that don't really tie to a mismatch error, but you never know...

    Does your larger file have "headers" in the first row for all columns of the data range?
    (if not, add headers)
    Does the larger file have any completely blank rows within the data?
    (if so, delete them - may not be relevant depending on if you are still using ".currentregion" to define the SourceData range)

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1