+ Reply to Thread
Results 1 to 2 of 2

pivot table confusion

Hybrid View

  1. #1
    Registered User
    Join Date
    03-12-2008
    Posts
    11

    pivot table confusion

    ive started out making a pivot table. it creates ok and i get what i need expect set of data is missing

    the following code is with recording, but i have recoded this into structured VBA with objects etc, to make it more dynamic. see under
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "InsideMarketData!R1C1:R16384C51").CreatePivotTable TableDestination:= _
            "[pivots.xls]Sheet38!R3C1", TableName:="PivotTable2", DefaultVersion:= _
            xlPivotTableVersion10
        ActiveWorkbook.ShowPivotTableFieldList = True
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Trader Name")
            .Orientation = xlRowField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Member Name")
            .Orientation = xlRowField
            .Position = 2
        End With
        ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
            "PivotTable2").PivotFields("Order ID"), "Count of Order ID", xlCount
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("T or F")
            .Orientation = xlColumnField
            .Position = 1
        End With
        Range("J9").Select
    End Sub
    this is the code i've made with the help of some on here. but with the Order ID part i cannot get what i desire...i dont know how to add

      ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
            "PivotTable2").PivotFields("Order ID"), "Count of Order ID", xlCount
    into this:

    '== set PivotTable Location and Create ==
    Set PT = PTCache.CreatePivotTable(TableDestination:=Cells(3, 1), _
             TableName:="Trader Compare", DefaultVersion:=xlPivotTableVersion10)
       
    '== assign fields into rows / columns ==
    PT.ManualUpdate = False
    
    '== position means columns across PT _
        such as position 1 means A _
                position 2 means B _
        and so on.
        
     
    '== add pivot table fields == goddam stupid moronic way of doing things
    With PT.PivotFields("Trader Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With PT.PivotFields("Member Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    With PT.PivotFields("Order ID")
    End With
    With PT.PivotFields("T or F")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    
    '== get rid of totals in Trader Name ==
    With PT.PivotFields("Trader Name")
        .Subtotals = Array(False, False, False, False, _
                            False, False, False, False, _
                            False, False, False, False)
                            
    End With
    grrr! thanks and sorry about all the code!!

  2. #2
    Registered User
    Join Date
    03-12-2008
    Posts
    11
    ok solved. hope this helps someone.

    PT.AddDataField PT.PivotFields("Order ID"), "Count", xlCount

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1