Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Mechanising 'bulk' calculated field creation (Office 2000 )

    I have just spent a long time creating and then formatting calculated fields, all of which follow a pattern. Looking at some of the threads here, I now realise that a bit of thought could have made this a lot easier, but I cannot find where PivotTable fields are defined for me to try vba to do the work for me.

    I have a large pivot table with a lot of contigious fields, and I want to create simple calculated fields from them. I have been using the calculated fields dialogue to
    a) add the fields 1 by 1.
    [img]/forums/images/smilies/cool.gif[/img] name them by a simple modification to their names. (I made a point of putting an underscore at the end of the column name so that I could make it unique in the pivot tale by removing it!)
    c) set the calculation, which is a simple division by a static field
    d) when complete manually adding them to the pivot table because it only does about 10 all by itself
    e) going to field setting and making the format % with no decimal point, one by one, by hand. I do this so that it is persistent as the dimensions of the table change.

    I tried recording a macro as a starting point but it ends up with highly specific code that I cannot seem to generalise.

    This must be possible to do in a couple of loops in vba, but I cant find the field references, and would probably struggle a litle with the syntax anyway.

    Thanks for any help,

    Mike c

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Mechanising 'bulk' calculated field creation (Office 2000 )

    If you can provide some code examples of specific code that you generate and how you want to generalize it, I think we could help you modify it.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Mechanising 'bulk' calculated field creation (Office 2000 )

    Steve,
    I'm at work at the moment and have had to re-create what I did last night. I goes like this for the format setting - I never even tought of adding the fields this way.

    Sub SetFieldProp()
    '
    ' SetFieldProp Macro
    ' Macro recorded 08/03/2004 by Mike C
    '

    '
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ( _
    "Sum of C18K [Diesel Parking Heater]")
    .NumberFormat = "0%"
    End With
    End Sub

    So my comments would be.
    a) I'd like it to happen to the active worksheet and pivot table, not the specific one i.e. the one I am in at the time.
    [img]/forums/images/smilies/cool.gif[/img] I'd like it to happen to the field I am in at the time, not a named one ("sum of...")
    c) I'd really like it to happen to more than the one field, I had in mind a loop from i=2 to pivotfieldcount or whatever it is

    Does this explain a bit?

    Mike c

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Mechanising 'bulk' calculated field creation (Office 2000 )

    Does this do what you want. It loops thru all the pivots in the activesheet and if the selection intersects the pivot, it sets the data format to the listed format.

    Steve

    <pre>Sub SetActivePivotFormat()
    Dim PT As PivotTable
    For Each PT In ActiveSheet.PivotTables
    If Not Intersect(PT.TableRange1, _
    Selection) Is Nothing Then _
    PT.DataBodyRange.NumberFormat = "0%"
    Next
    End Sub</pre>


  5. #5
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Mechanising 'bulk' calculated field creation (Office 2000 )

    Steve,

    thanks, I think you have set the cell format where I wanted to set the filed property. The difference is theat the field property will be persistent as the pivot table changes shape. Can you get at the field property?

    Mike C

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Mechanising 'bulk' calculated field creation (Office 2000 )

    If I understand, this should work. It will go thru all the datafields and change the format.

    Steve

    <pre>Sub SetActivePivotFormat()
    Dim PT As PivotTable
    Dim PF As PivotField
    For Each PT In ActiveSheet.PivotTables
    If Not Intersect(PT.TableRange1, _
    Selection) Is Nothing Then
    For Each PF In PT.DataFields
    PF.NumberFormat = "0%"
    Next
    End If
    Next
    End Sub</pre>


  7. #7
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Mechanising 'bulk' calculated field creation (Office 2000 )

    Steve,

    Thanks very much that works a treat. I still dont understand the objects but then you have done this for me!

    Mike C

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Mechanising 'bulk' calculated field creation (Office 2000 )

    There are only 2 objects I worked with:
    PT which is a (generic pivot table)
    and
    PF which is a generic Pivot field
    In the "PT" we look at:
    PT.TableRange1 is the range of pivot table "PT"
    PT.DataFields is the "collection" of datafields in Pivot table "PT"
    In "PF" we look at:
    PF.NumberFormat is the format of the field PF

    The code loops thru each pivot table of the active sheet. If the range of the pivot table does NOT intersect with the selection then we have not chosen this pivottable and we go on.

    If it does intersect, then we work on this pivot table:
    We loop thru all the datafields in the pivottable and each one we change the numberformat

    Hope this is clearer

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •