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
0
Utf
3/31/2010 9:25:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
785 Views

Similar Articles

[PageSpeed] 29

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
0
Roger
4/1/2010 9:02:33 AM
Reply:

Similar Artilces:

How do I add a total to a stacked bar graph in Excel
I am using Excel for some graphs in Powerpoint and we are using stacked bar graphs. Does anyone know how to add a total on a stcaked graph? I can add individual values but I am looking for total per bar. the simplest way is to copy the data with the totals into the chart. select the section of the bar equal to the totals and change the axis to secondary add the series values and in paterns select none for fill and line. in th elegehgend select the line for the totals and clar all. "Greg L" wrote: > I am using Excel for some graphs in Powerpoint and we are using stacked bar...

macros in excel #4
Is it possible to set up a macro in excel to print report with conditions "Jai" wrote: > Is it possible to set up a macro in excel to print report with conditions Can you elaborate on "Conditions" and what kind of report it is? May be I can pitch in .. I am a beginner too, but I can look around. I am trying to set up a report from database of vendors. Each vendor has it's own number. Some vendors are grouped together. In report, there should be list of each vendors and all grouped vedors should have subtotal for its data. I know it's easy to do a subt...

export excel macros
I am gettting a new computer from my IT staff this week. Is there a way to backup all my personal macros. I have done a search for personal.xls and have not come up with anything. Can anyone provide answers. Brad Wilson nctarheelfan@hotmail.com Hi search for your personal.xls in your xlstart folder -- Regards Frank Kabel Frankfurt, Germany Brad wrote: > I am gettting a new computer from my IT staff this week. Is there a > way to backup all my personal macros. I have done a search for > personal.xls and have not come up with anything. Can anyone provide > answers. &...

Excel Database
I'm developing a database in Excel and I need some help with macros. The scenario: It's a database of drink short takes for a bar. Each tab has a different summary category, such as spirits, wines, cocktails, etc... Cells in each summary tab links to a new tab, where the data for that entry is explained (the short take sheets themselves). Short take planner Now, imagine a simple monthly calendar, each cell corresponding to each day of the month that has a drop down list of values to choose from. First, I would like to make this drop down list show data from the...

Preserving Cell Formats in Excel Query
I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't carry through to the query results. Is there a way to carry original formatting through to Excel Query results Any insight would be appreciated Karen S No, you can import the data, but not the formats. If you're importing programmatically, you could apply the formatting as part of the import procedure. Karen S wrote: > I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't car...

Hidden data when pasting from html page into Excel?
I have a situation where I copy a report, which is generated as an html page, into Excel for analysis. When a new copy/version of the report is available, I copy and paste the new data over top of the old in Excel. I've noticed that the size of my Excel file is growing after each copy/paste situation. To manage this, I can delete the worksheet where I paste the data and reconstruct it. After doing nothing else, saving the workbook results in an immediate reduction of file size. If I simply select everything on the sheet and delete, the file size does NOT go down; I have to delet...

Chart wizard funcitonality in Excel 2007?
I hope I'm missing something in Excel 2007. In Excel 2003, the chart wizard gives you access to lots of options when you create a graph--graph type, data range, the various series (including the X axis labels), the chart title and axis titles, gridlines, legends, and others. It doesn't take long to create the graph with most of the options you want and then tweak it pretty quickly. In Excel 2007, it seems that every option is a separate button/menu/choice. Setting the chart title, the x-axis title, and the y-axis title are three different operations. Ditto for pretty much everyt...

divide so no error Excel 2007
I have a simple issue. I have two cells with numbers and I want to divide one by the other and set it up on many cells. Problem is if I enter the folmula befor there are actually numbers in the two cells I get the Divide by 0 error (#DIV/0!) So How can I divide A2 / B2 only if it won't produce an error? =IF(B2=0,0,A2/B2) -- Regards, Peo Sjoblom "Striker" <striker3070@qwest.net> wrote in message news:%23CnsJAoRJHA.5244@TK2MSFTNGP04.phx.gbl... >I have a simple issue. I have two cells with numbers and I want to divide >one by the other and set it up ...

Historical Excel question re: statistical capabilities
Does anyone know approximately when Excel started shipping with statistical functions built in- or know how I can find out? In the pre-spreadsheet days of my computing past, I used BASIC to code statistical functions necessary for my profession. As spreadsheets became more advanced, we all transitioned to using the advanced functionality. My question is when did this occur? Did Excel always have its current complement of statistical functions? Thanks slingsh0t@hotmail.com shared this with us in microsoft.public.excel.misc: > Does anyone know approximately when Excel started shipping...

2 Email domains w/2 Act. Dir. Domains - Same Forest
Hello, Scenario: We've had domain1.com (AD) setup and running in exchange 2003 for a year now w/mixed win2k and win2k3 servers - email domain name is the same, domain1. OWA works great and haven't had any problems. Our company is branching out into a new business segment and I've created a new active dir. domain within the same forest, domain2.local. It is NOT a child domain, but does sit under the same forest. I need to configure my front end/back end exchange 2003 boxes to do the following: -receive email for both email domains (domain1.com and domain2.com) -let users...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

get paid now!!!
when you subscribe you'll be rewarded 10$$$ Check it now,don't wait!!!!!!! http://e-mailpaysu.com/members/index.cgi?andrusko ...

Add the same field twice to a pivot table but filter one of them?
In my datasheet, I have a "cost" column and a "date" column so each cost has an associated date. In my pivot table, I've added the "cost" as a field, which shows the total and this is fine. However, I'd like to add the "cost" as a field again and this time selecting which dates to include in the cost number so that I have two cost fields side by side. Is something like this possible? Hi That is not possible in the same PT. You will need to set up a second PT based upon the same data set as the first but do NOT use the same Pivot Cache to save ...

Bizarre Sales Tax Table Issue
I have four taxes set up in my system, and confirmed in the TAX table in the database: ID Description Rate 1 Sales Tax 0% 2 VAT 15% 3 VAT-EX 0% 4 VAT-ZR 0% If I create a new item, and select VAT, then check the ITEM table, the TaxID = 3 for that item. You would think that the system would apply the VAT-EX taxation on the item. However, when i do a transaction, it puts the 15% rate on the item and everything seems work out okay, including the reports, which show that VAT was collected. The TransactionEntry table has the proper sales ...

how do i get font style back on toolbar?
new to this...when i started my document the font style was displayed on toolbar...now it is not...what do i do and thanks to those who help!!!!!!1 Melissa, try view, toolbars, and check formatting -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "melissa" <melissa@discussions.microsoft.com> wrote in message news:852099EC-03A0-4B0F-91AD-0B9F61CF8C4B@microsoft.com... > new to this...when i started my document the fon...

opening excel worksheet
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel whenever I try to open an existing excel worksheet it opens very short. I have to drag it down to the bottom of the screen, is there a setting that i need to adjust? No, simply SAVE the workbook after you adjust it. Next time, it will open at the size you want. On 27/03/10 6:42 AM, in article 59bb6065.-1@webcrossing.JaKIaxP2ac0, "scrapper@officeformac.com" <scrapper@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel > wh...

Get Mallika Sherawath Bathing Videos in all Angles
Get Mallika Sherawath Bathing Videos At http://blogcreationandhosting.blogspot.com/2009/11/1-per-click-in-adsense-programs.html < Due to some issues,I have hidden these videos inside an image.In that website , click on the big vertical Image on right side of website & get the videos, Enjoy it > ...

Can I get my Dialog's background color already? :) Just can't figure it out
I'm trying to get the background color for the basic Dialog created using the Wizard GetSysColor(COLOR_WINDOW) isn't working (returns some other color) GetBkColor() for the DC isn't working (returns white) Where can I trap that? The dc->GetBkColor in OnPaint returns a white color I finally did GetSysColor(COLOR_3DFACE) because I noticed they are the same, but that's not really what I want Thank you. "Yasoo" <anonymous@discussions.microsoft.com> wrote in message news:D9FF1E6B-C815-41B0-92FA-846EDC82FBAE@microsoft.com... > I'm trying to get the ba...

count a pair of numbers in row in a table
Hello, my question is: we have the following table: 34 29 13 15 7 15 8 40 11 24 13 6 8 21 38 9 17 23 1 4 22 38 42 37 16 1 18 11 37 41 5 42 18 33 45 9 1 21 41 15 41 1 27 23 42 23 29 7 38 18 42 12 26 34 36 and this one in another sheet 1 2 3 1 2 3 I want to fill the second table with the sum of how many times the numbers if each row and column appear in the same row in the first table. for example: how many time the numbers 2 and 3 appear together in the same row on the first table Nik, Assume t...

How do I turn the pivot chart into a list with all cells filled?
I have the pivot chart and would like to copy and paste it so that all fields are filled Select the pivot table. Choose Edit>Copy Select the cell where you'd like to paste the copy Choose Edit>Paste Special Select Values, click OK There are instructions here for filling the blanks: http://www.contextures.com/xlDataEntry02.html gianna wrote: > I have the pivot chart and would like to copy and paste it so that all fields > are filled -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Using Queries in Excel
What are the best practices for using database-like queries in Excel. Let's say you wish to join to sheets together och view a subset of columns in a third sheet. I've tried several different methods, but I dont think any of them are completely good. I've used VLookup, Index, MS Query. (MS Query must be the must forgotten MS product in history. It's like a time machine back to Windows 3.11) I've also tried alot of different methods for searching a range, based on more than one criteria, and display the result, either a single value or a sum based on several rows. Here i&#...

Readonly a linked table
I have a table that gets generated every month, and we use as a source of data for other databases. We link this, but I was wondering if there is a way to make that linked table read only. Any ideas? Thanks so much, Chris M. hi Chris, mcescher wrote: > I have a table that gets generated every month, and we use as a source > of data for other databases. We link this, but I was wondering if > there is a way to make that linked table read only. Either make the back-end read-only or use a pass-through query instead of a linked table. mfG --> stefan <-- ...

Best tool for adding a pivot chart like chart to a report?
Is it the PivotChart form or should I used the MSChart OCX? Which is easier to set up/configure to display the data properly? (A multi-column, multi-entity bar chart) Which is the most flexible? And where can I find tutorials on using both? Thanks Kyle Jedrusiak ...

Import plain text with formulas into Excel
I'm having some trouble in Excel. If I create a plain text file like the following: 1,2,3 4,5,6 =sum(a1:a2), =sum(b1:b2) Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications. I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for... Any good sugestions? Regards -- schmiedel ---------------------------------...

cannot filter excel files in "files of type"
I have Outlook 2003, and when I try to add an attachment, I cannot filter based on Excel files in "Files of type". Excel is the listed but other like Word are. The entire Office 2003 suite is installed on the PC and I tried reinstalling to no avail. Also, Customize is grayed-out under the tools menu in Outlook\new mail message. Please help me out. thanks. Sorry, I mean to say Excel is NOT listed under "Files of Type" "wli2k2" wrote: > I have Outlook 2003, and when I try to add an attachment, I cannot filter > based on Excel files in "Files ...