Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Pivot table back to Data Table | Excel Discussion (Misc queries) | |||
convert Pivot table back to Data table | Excel Discussion (Misc queries) | |||
Pivot table: hyperlink back to source data | Excel Discussion (Misc queries) | |||
Vlookups in a Pivot table brining back OFFSET Data | Excel Worksheet Functions | |||
Transforming pivot table back to the source table | Excel Worksheet Functions |