Macro to change the PivotField to sum

I'm trying to find/creat a macro that will automatically change the Field in 
my pivottable to Sum.  The problem that I'm running into is when I have have 
a new field name or a new pivottable name.  I can creat one where the 
pivottable and the PivotField are always the same but that really doesn't 
help me. Any help would be greatly appreciated.

Thanks
Vick
0
Vick (9)
12/21/2005 6:49:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
325 Views

Similar Articles

[PageSpeed] 28

Debra Dalgleish has an addin that you may like.
http://www.contextures.com/xlPivotAddIn02.html

The code is unprotected, so you could just extract that portion and include it
your macro if you want.



Vick wrote:
> 
> I'm trying to find/creat a macro that will automatically change the Field in
> my pivottable to Sum.  The problem that I'm running into is when I have have
> a new field name or a new pivottable name.  I can creat one where the
> pivottable and the PivotField are always the same but that really doesn't
> help me. Any help would be greatly appreciated.
> 
> Thanks
> Vick

-- 

Dave Peterson
0
petersod (12005)
12/21/2005 7:25:28 PM
I tried pulling out the code for the macro I want, but it appears I receive 
an error saying sub or function not displayed and it highlights the 
PivotCheck line. I'm not sure what this is calling.

Vicks 

"Dave Peterson" wrote:

> Debra Dalgleish has an addin that you may like.
> http://www.contextures.com/xlPivotAddIn02.html
> 
> The code is unprotected, so you could just extract that portion and include it
> your macro if you want.
> 
> 
> 
> Vick wrote:
> > 
> > I'm trying to find/creat a macro that will automatically change the Field in
> > my pivottable to Sum.  The problem that I'm running into is when I have have
> > a new field name or a new pivottable name.  I can creat one where the
> > pivottable and the PivotField are always the same but that really doesn't
> > help me. Any help would be greatly appreciated.
> > 
> > Thanks
> > Vick
> 
> -- 
> 
> Dave Peterson
> 
0
Vick (9)
12/21/2005 8:35:02 PM
This is the code I meant (from the modData module):

Option Explicit

Sub SumAllData()
'changes data fields to SUM
On Error GoTo errHandler

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Set ws = ActiveSheet
Application.ScreenUpdating = False

If PivotCheck(ws) Then
  For Each pt In ActiveSheet.PivotTables
    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = xlSum
    Next pf
    pt.ManualUpdate = False
  Next pt
Else
  MsgBox "There are no pivot tables on the active sheet"
End If

exitHandler:
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
  Application.ScreenUpdating = True
  Exit Sub

errHandler:
  GoTo exitHandler
End Sub

'But make sure you include this portion, too (from the modPTCheck module):

Function PivotCheck(ws As Worksheet) As Boolean
  
  PivotCheck = False
  
  If ws.PivotTables.Count > 0 Then
    PivotCheck = True
  End If
  
End Function

Vick wrote:
> 
> I tried pulling out the code for the macro I want, but it appears I receive
> an error saying sub or function not displayed and it highlights the
> PivotCheck line. I'm not sure what this is calling.
> 
> Vicks
> 
> "Dave Peterson" wrote:
> 
> > Debra Dalgleish has an addin that you may like.
> > http://www.contextures.com/xlPivotAddIn02.html
> >
> > The code is unprotected, so you could just extract that portion and include it
> > your macro if you want.
> >
> >
> >
> > Vick wrote:
> > >
> > > I'm trying to find/creat a macro that will automatically change the Field in
> > > my pivottable to Sum.  The problem that I'm running into is when I have have
> > > a new field name or a new pivottable name.  I can creat one where the
> > > pivottable and the PivotField are always the same but that really doesn't
> > > help me. Any help would be greatly appreciated.
> > >
> > > Thanks
> > > Vick
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12005)
12/21/2005 10:34:35 PM
Reply:

Similar Artilces:

Changing Text on Button Question...
Okay, before I lay out the question I am going to give you a little overview about the form that I am using. My form has 4 buttons on it. 1 for adding a record, another for finding a record, another for deleting a record and the last to close the form. Now for my question, I want to know if there is a way to change the text on my add button from "Add Record" to "Update Record" after the find button is clicked and the form is searched. I want the add button to the the same function all the time. I just want the text to switch when the find button is clicked. And after ...

No option to delete a change to a PO in Great Plains
Open an existing PO, Make a change and then try to close the PO without saving the change. The system only allows to Save or Cancel closing the PO. There is not an option to close without saving the changes. This problems exists for Sales orders and other documents in the system. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based News...

How to change bk color of a dialog to match CTabCtrls' tab in XP?
Hi, I'm making some mods to an app that was developed for W2K with VS2003. I thought it would be a good time for the app to get the XP look when it runs on the OS. Adding manifest seemed like a simple and quick way to do that. Unfortunately I ran into unexpected problems with CTabCtrl. On W2K default background colors for dialog boxes and tab controls were identical. This is not the case on XP - tab controls' background color is much lighter. As a result, tab control's content (i.e. embedded dialog with controls) has a different background color than the rest of the ta...

Publisher Newbie looking for Record Macro
Guys/Gals Is the old 'Record Macro" available in Publisher 2003. I am new to Publisher but and old hand with Office and VBA. I need to create some print looping macros for Publisher but cannot find The old record option Any Clues ?? T.I.A. Greeny A small child turns to Ed, and exclaims: "Look! Look! A post from Greeny <greeny@nospam.gisoz.com>!"... > Is the old 'Record Macro" available in Publisher 2003. Nope. Publisher didn't introduce Record Macro functionality with its VBA tools > I am new to Publisher but and old hand with Office and ...

Charts dont refresh to show changes to data sheet
Hi All I have a reporting system in Excel with all my charts on one sheet and then one sheet per chart with the data on. When the user changes different configuration and data sources my VBA whirs away and modifies all the data sheets as appropriate. What has happened is that all of a sudden changes made on the data sheet are not automatically reflected on my charts. For example, even something simple like changing the name of a series on a data sheet is not reflected in my chart! I try a F9 to force calculation and still nothing. I try to reset the source data and it still does not update ...

Opening Excel changes screen resolution. How do I stop this ?
Whenever I open Excel it changes my screen resolution to a lower resolution. How can this be prevented ? After further investigation, I answered my own question. For some reason EXCEL.EXE properties was set to open using 256 colors. I unchecked the option and everything started to work correctly. For those who encounter this problem again here is how you fix it: 1. Go to C:\Program Files\Microsoft Office\OFFICE11 2. Right click on EXCEL.EXE and select "Properties" 3. Select the "Compatability" tab 4. Check the display settings and ensure that they are what you wan...

Cannot change credit information
v.2005 Standard -- In the Change Account Settings area, in the Credit Information section, I cannot change the interest rate, minimum payment, etc... There is no button or link which will let me edit those fields. This is for existing accounts and new accounts. I didn't have this problem with v.2004. What's wrong? Please help! In microsoft.public.money, Shawn T wrote: >v.2005 Standard -- In the Change Account Settings area, in the Credit >Information section, I cannot change the interest rate, minimum payment, >etc... There is no button or link which will let me...

Macro optimizing
How can i stop all the graphical displays while im activating a Macro to speed up the work, especially when it consist of transporting data through multiple workbooks? Hi Joseph, Turn off screen updating and calculation within the macro, and turn back on when finished. See the following two web pages. Proper, and other Text changes -- Use of SpecialCells http://www.mvps.org/dmcritchie/excel/proper.htm Slow Response and Memory Problems http://www.mvps.org/dmcritchie/excel/slowresp.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://...

Workflow
Hi There I have a entity which has a workflow associated with it. This workflow creates a task for the user(owner) and waits for it to be "completed or cancelled". once completed it shoots off an email. the problem i am facing is, how do i capture the task status( completed or cancelled) and put it in an email. i can't see anyway for me to capture the task status and store it in an entity field - whcih i can use in the email. has anyone found a way around it. thanks a lot for your help Hi Chanpreet, It is difficult to get your requirment in working state with workflow&...

Macro to print to non-default printer
OK, this has me completely stymied. I have made macros to send print jobs to different printers around the office - my default printer, a large printer for massive jobs, and another printer for medium sized jobs. Two of them work, one I can't get to work and it's driving me crazy. I'm on Word 2007. I created all three the same way - record macro, then just went through the steps of clicking on the Office button, selecting print, manually selecting the printer I want, hit print, end recording. They work fine. The third gives me "printer error" every tim...

Unable to save macros in word 2007
The word environment is setup to share macros using the normal.dotm located in a shared drive. This is configued in WordOptions.Advanced.FileLocations.UserTemplates However, a recorded macro will not save in normal.dotm or in the current document. For instance, a simple standard process to illustrate the problem is to Open a blank document. Use "Record Macro" from the Developer tab. The macro is named in the popup and Document1 is selected from the "Store Macro in" pulldown. Click Ok. Write a few letters. Use "Stop Recording". Save the docum...

Sum Crossreference
Let's see if I can set this up right... I have a workbook with three sheets. Sheet2 column A has a list of geographical abbreviations. Column B has a list of garage addresses. Columns D through BY (that's 76 columns) has a list of sub-geographical areas in each row that the garage in Column B is responsible for. Each garage is responsible for unique sub-geographical areas and there are several garages in each parent geographical area. Sheet3 Column B has a list of those same sub-geographical areas. Column C has the amount of work performed in each sub-geographical area ...

font changed color
When I try and send a new message the font somehow changed from black to yellow? How can I change the default color back to black Thanks in advance Hi, To change the default font settings in Outlook follow these steps. Open Outlook, click on Tools > Options > Select the Mail Format tab > Click on the Fonts button From here you can customize the font settings. Regards, Scott Atkins MCSE, MCSA Partner Technical Lead - Outlook Microsoft Technical Support for Platforms and Business Applications -------------------- >From: PapaBear <PapaBear@redridinghood.com> >Newsgroups...

Changing many charts in one worksheet
I have a worksheet with over 100 similar small datatables and charts. It has been asked of me that I now chart one more column of data from each table. The data is there and I can record a simple macro to plot the additional series from the first table to the first chart. My question is, how do I get the macro to go to the next table and plot the additional data to the next chart. The data is always in the same column, different rows. I tested this simple macro on a worksheet with three charts. Each used data from B:D to populate two series and there was additional data in E. The m...

Changing Line in line chart
does anyone know how to chnage a line in a line chart making one end an arrow and making the entire line very thick (thicker than Excel allows in the drop down box) Thanks > does anyone know how to chnage a line in a line chart making one end an arrow > and making the entire line very thick (thicker than Excel allows in the drop > down box) This can be done with a macro, by drawing a polygon shape over the line chart series. With little explanation, the code is given below. The chart must be a line chart with a category, not time scale, axis. In the block of code that begin...

Elapsed Time Macro
I have a worksheet that contains sensitive data. When it is used, some of the users tend to accidentally leave the data displayed and walk away from it, allowing the data to be read by others who pass by. I would like to have a macro or some other means of automatically changing the screen display to a different worksheet (or blank for that matter) after a fixed amount of time without a keystroke - perhaps one minute or so of elapsed time without a keystroke. Is there a practical way to do this? It would be very difficult to implemement, why not just activate the screen saver to lock the...

Onkey vs Macro Short cut key
Is there a benefit to using the macro short cut key in macro options vs coding a key combo using Onkey? Thanks Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11350 View this thread: http://www.excelforum.com/showthread.php?threadid=385952 I don't think there is a huge difference but with the macro options approach the shortcut is almost always there. For instance it takes a macro to run to set the Onkeys but not for macro options. And to extend ...

Can I change the shape of a picture frame?
I'd like to create a picture frame with a shape other than a square/rectangle (i.e. oval, octagon, ect.) so that a picture that is inserted in that frame will take that shape. Is there a way to do this in Publisher? Any help would be greatly appreciated! -- Thank you, KDill You can draw a shape with the freeform tool in autoshapes. Your picture will not take the shape of the frame, basically the picture will be inserted as a best fit. However, if you select the picture, right-click, edit points... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news...

change a cell colour when entering a letter
I am working in excel 2000, and I need to know how to change a colour of a cell when I enter a letter. Ie, if I put y for yes in a cell, I want to change that cell to say red. Use "Conditional Formatting" Click on the cell, do Format > ConditionalFormatting > CellValueIs > EqualTo > Y > FormatButton > PatternsTab > choose red color block > OK > OK Vaya con Dios, Chuck, CABGx3 "paul42770" <paul42770@discussions.microsoft.com> wrote in message news:07A6CCBF-7ECE-4507-AA29-C914FEA012EF@microsoft.com... > I am working in excel 2000, a...

How to change the public folder Conflict Messages
I have public folders which seem to be generating some conflicts. this is not the problem the problem is they are being sent to all in the company and i want to change this but dont know where it is i need to go to make this change cant seem to find it any where. Thanks for any help ...

About Change of UOM ID
Would there be any tools/methods to change the unique ID for UOM? ...

Creating resizing macro
Hi, I'm trying to create a macro in Excel so that I can click onto a chart and resize it without having to select the "Select Objects" arrow in the drawing toolbar (this is for very PC illterate folk). I recorded the macro and got this text: Sub Chart_two_third_width() ' ' Chart_two_third_width Macro ' Macro recorded 03/04/2008 by IT ' ' ActiveSheet.Shapes("Chart 1").Select Selection.ShapeRange.LockAspectRatio = msoFalse Selection.ShapeRange.Height = 178.5 Selection.ShapeRange.Width = 340.5 With Selection .Placement ...

Should we change the DNS or MX
Our current mail server is listed in external DNS as 'exchange.domain.com' (this is also our MX record name and internal server name), but we want to change it to mail.domain.com. What is the best way to handle this without changing the name of the server? should we just change the external dns, internal mx record, but create some sort of internal redirect to the right internal server? Thanks circulent wrote: > Our current mail server is listed in external DNS as 'exchange.domain.com' > (this is also our MX record name and internal server name), but we want to &g...

Unable to reconcile changes for entitity (Business Portal)
I am trying to approve a requisition in Business Portal, and when I do, I receive a message: "Error in the application" "Unable to reconcile changes for entity". Any idea what might be causing this error, or how to resolve it? Thanks, KJ Hi KJ Are you still having problems? Please make sure you have your cost categories setup with each expense project. It could also be General Ledger numbers missing from the expense categories. AZ "KJ" wrote: > I am trying to approve a requisition in Business Portal, and when I > do, I receive a message: "Error...

Combining two similar worksheets and showing changes
I thought i posted this question yesterday, obviously i have no idea what im doing, because now i can't find it. But anyway, In excel I have two very similar worksheets that started out as the same worksheet. They were copied by someone and now two users have been making changes to them, and i guess its my job to put them back together, and show the changes that both of the users made. if i just copied one to the other i would have nearly five thousand rows of duplicate data. How can i do this? It seems like it would be so simple, I just don't work with excel that much. th...