Excel Pivot table - getting back to the skeleton template
in [General]
|
Prev: How do I convert rows to columns
Next: Problem with Excel getting correct # of records back from Access
From: georgec7 on 31 Mar 2010 17:25 Hi, I hope somebody can help - I'm new to the forum so please be gentle - and thanks in advance. I'm sure there is a simple solution to this, but I'm now starting to bang my head! Here goes - Using Excel 2003 - Importing data from SQL server database. The users love the ability to be able to chop and change the data but have asked for an addition. When the pivot table is first opened, you are presented with the skeleton template (that's what I'm calling it anyway). So the user drags fields from the pivot table list into data rows and data columns to start to build up a picture. This is fine, but some users don't want to drag and drop - they want a formatted view with rows and columns predefined. I can do this using macros and then form buttons - the user clicks on the button and is presented with the data -behind the form is just the macro. My problem is - unless, the data currently being displayed in the pivot table is cleared down, the next macro won't work. So I need to create a macro, or a step in the report macros to first of all clear down existing data so that excel is starting from the skeleton template and then building the new view. If I try to run macros without clearing down first manually, the macros just don't work . Any ideas - I hope I haven't made this too complicated. Thanks George
From: Roger Govier on 1 Apr 2010 05:02
Hi George Try ActiveSheet.PivotTables("PivotTable1").ClearTable -- Regards Roger Govier georgec7 wrote: > Hi, > > I hope somebody can help - I'm new to the forum so please be gentle - and > thanks in advance. I'm sure there is a simple solution to this, but I'm now > starting to bang my head! Here goes - > > Using Excel 2003 - Importing data from SQL server database. > The users love the ability to be able to chop and change the data but have > asked for an addition. > > When the pivot table is first opened, you are presented with the skeleton > template (that's what I'm calling it anyway). So the user drags fields from > the pivot table list into data rows and data columns to start to build up a > picture. This is fine, but some users don't want to drag and drop - they want > a formatted view with rows and columns predefined. I can do this using macros > and then form buttons - the user clicks on the button and is presented with > the data -behind the form is just the macro. > > My problem is - unless, the data currently being displayed in the pivot > table is cleared down, the next macro won't work. So I need to create a > macro, or a step in the report macros to first of all clear down existing > data so that excel is starting from the skeleton template and then building > the new view. If I try to run macros without clearing down first manually, > the macros just don't work . > > Any ideas - I hope I haven't made this too complicated. > > Thanks > > George |