Excel Pivot table - getting back to the skeleton template

[georgec7]

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

 

[Roger Govier]

Hi George

Try
ActiveSheet.PivotTables("PivotTable1").ClearTable

 

[George Cruickshanks]

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/tutorial...2e-39384482c80e/featured-product--servic.aspx

 

[Roger Govier]

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.