+ Reply to Thread
Results 1 to 3 of 3

Convert Column Data Fields to Row Data Fields in pivot table

  1. #1
    Registered User
    Join Date
    02-08-2014
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    7

    Convert Column Data Fields to Row Data Fields in pivot table

    Country Hour Data Total Data
    Austria - A1 0 Sum of SeiA 51 Country Hour Sum of SeiA Sum of SeiT
    Sum of SeiT 4.88 Austria - A1 0 51 4.88
    1 Sum of SeiA 56 1 56 2.83
    Sum of SeiT 2.83 2 33 1.21
    2 Sum of SeiA 33 3 9 0.27
    Sum of SeiT 1.21 4 26 0.38
    3 Sum of SeiA 9 5 105 1.51
    Sum of SeiT 0.27 6 330 4.37
    4 Sum of SeiA 26 7 410 9.39
    Sum of SeiT 0.38 8 581 14.68
    5 Sum of SeiA 105 9 708 21.64
    Sum of SeiT 1.51 10 820 25.7
    6 Sum of SeiA 330 11 832 27.2
    Sum of SeiT 4.37 12 1020 27.38
    7 Sum of SeiA 410 13 1111 29.75
    Sum of SeiT 9.39 14 959 27.25
    8 Sum of SeiA 581 15 996 22.03
    Sum of SeiT 14.68 16 908 25.65
    9 Sum of SeiA 708 17 927 30
    Sum of SeiT 21.64 18 927 32.7
    10 Sum of SeiA 820 19 856 36.27
    Sum of SeiT 25.7 20 803 34.48
    11 Sum of SeiA 832 21 574 33.98
    Sum of SeiT 27.2 22 475 23.97
    12 Sum of SeiA 1020 23 251 17.95
    Sum of SeiT 27.38 Austria - A1 Total 13768 455.47
    13 Sum of SeiA 1111 BELGIUM - Canary 0 12766 541.670004
    Sum of SeiT 29.75 1 8572 300.769999
    14 Sum of SeiA 959 2 5802 180.02




    Guys left side pivot created in vb 6.0 & right side pivot table created manually in excel.

    i want to generated pivot table using vb 6.0 same as right side pivot.


    Set PRange = ws1.Range("R1:Y" & finalrow)
    Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
    Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1), TableName:="PivotTable1")


    With PT.PivotFields("Country")
    .Orientation = xlRowField
    .Position = 1
    End With

    With PT.PivotFields("Hour")
    .Orientation = xlRowField
    .Position = 2
    End With

    With PT.PivotFields("SeiA")
    .Orientation = xlDataField
    .Function = xlSum
    .Position = 1
    End With

    With PT.PivotFields("SeiT")
    .Orientation = xlDataField
    .Function = xlSum
    .Position = 2
    End With
    Attached Images Attached Images

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Convert Column Data Fields to Row Data Fields in pivot table

    Hello - please wrap CODE tags around your code, as it says in the rules...


    To answer your question - Add this section to the end of your code:
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    02-08-2014
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Convert Column Data Fields to Row Data Fields in pivot table

    thanks a lots it works.....

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sort pivot table on data fields
    By Pala in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-28-2012, 07:06 AM
  2. Excel 2007 : Macro to add Pivot table Data fields
    By vikrantm26 in forum Excel General
    Replies: 1
    Last Post: 07-09-2010, 08:49 AM
  3. How to Disable automatic fields in pivot table like total and count on fields
    By anushka in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-24-2009, 07:53 AM
  4. Differentiate between column fields and data fields in a pivot table
    By whiteheadw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2009, 01:59 PM
  5. Replies: 1
    Last Post: 03-06-2009, 12:45 PM

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