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

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.

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.

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

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.

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

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

Do the variables reference valid ranges?

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