Excel Pivot table - getting back to the skeleton template
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 |
Excel Pivot table - getting back to the skeleton template
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 |
Excel Pivot table - getting back to the skeleton template
Hi Roger - unfortunately Cleartable is for Excel 2007 - I'm using Excel 2003 - I did try it, but got a debug error. I need to be able to do the equivalent of ClearTable in 2003.
Thanks anyway. George Roger Govier wrote: Hi GeorgeTryActiveSheet.PivotTables("PivotTable1"). 01-Apr-10 Hi George Try ActiveSheet.PivotTables("PivotTable1").ClearTable -- Regards Roger Govier georgec7 wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Featured Product / Service Review: TekPub http://www.eggheadcafe.com/tutorials...t--servic.aspx |
Excel Pivot table - getting back to the skeleton template
Hi George
Here's a macro from Debra Dalgleish that will do the job in XL 2003 Sub ActiveCellClearPivot() On Error Resume Next Dim pt As PivotTable Dim pf As PivotField Set pt = ActiveCell.PivotTable For Each pf In pt.VisibleFields pf.Orientation = xlHidden Next pf 'pt.RefreshTable End Sub Place your cursor on a cell within the PT before running. -- Regards Roger Govier George Cruickshanks wrote: Hi Roger - unfortunately Cleartable is for Excel 2007 - I'm using Excel 2003 - I did try it, but got a debug error. I need to be able to do the equivalent of ClearTable in 2003. Thanks anyway. George Roger Govier wrote: Hi GeorgeTryActiveSheet.PivotTables("PivotTable1"). 01-Apr-10 Hi George Try ActiveSheet.PivotTables("PivotTable1").ClearTable -- Regards Roger Govier georgec7 wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Featured Product / Service Review: TekPub http://www.eggheadcafe.com/tutorials...t--servic.aspx |
All times are GMT +1. The time now is 12:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com