vba Pivot Table creation

[GreenyMcDuff]

Hi all,

I am trying to create a Pivot Table using VBA.

I am ok with VBA code but no good at all with Pivot Tables so please bear with me.

I found this code on the net that I am trying to manipulate:

Sub CreatePivot()
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    Set WSD = Worksheets("PivotTable")
    ' Delete any prior pivot tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT
    ' Define input area and set up a Pivot Cache
    FinalRow = Sheets("Data").Cells(Application.Rows.Count, 2).End(xlUp).Row
    FinalCol = Sheets("Data").Cells(20, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = Sheets("Data").Cells(6, 2).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange)
    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, FinalCol + 2), TableName:="Client Pivot Table")
    ' Turn off updating while building the table
    PT.ManualUpdate = True
    ' Set up the row & column fields
    PT.AddFields RowFields:=Array("Sum of fund under managment", "% TOTAL"), _
        ColumnFields:="Region"
    ' Set up the data fields
    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
    End With
    ' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
End Sub

I have a data table set up in a worksheet named "Data".
The data starts in cell B6 and varies in size (hence FinalRow and FinalCol variables)

The data sheet looks like this:

<TABLE style="WIDTH: 676pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=900 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 229pt; mso-width-source: userset; mso-width-alt: 11154" width=305><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 186pt; mso-width-source: userset; mso-width-alt: 9069" width=248><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #95b3d7; WIDTH: 53pt; HEIGHT: 22.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl29 height=30 rowSpan=2 width=70>Branch Code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #95b3d7; WIDTH: 229pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl29 rowSpan=2 width=305>Branch Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #95b3d7; WIDTH: 61pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl29 rowSpan=2 width=81>Class Code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #95b3d7; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl29 rowSpan=2 width=77>Class Type</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #95b3d7; WIDTH: 89pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl29 rowSpan=2 width=119>Sum of fund under management in EUR</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #95b3d7; WIDTH: 186pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl29 rowSpan=2 width=248>Fund Name</TD></TR><TR style="HEIGHT: 11.25pt" height=15></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl24 height=17 width=70> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>A40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>GLOBAL CONVERTIBLE FUND</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 11.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl28 height=15 width=70>ACA80001</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>WILFRID LAURIER UNIVERSITY BALSILLIE ENDOWMENT PLAN</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>BB0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>I HEDGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num="945770.65757504757">945,770.66</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>GLOBAL HIGH YIELD BOND FUND</TD></TR></TBODY></TABLE>

And I want the Pivot Table to look like this:

<TABLE style="WIDTH: 276pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=367 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 11.25pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl24 height=15 width=98> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 157pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl24 width=209>Data</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl25 width=60> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl24 height=15>Branch Name</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl24 x:str=" Sum of fund under management in EUR "> Sum of fund under management in EUR </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl26 x:str=" % TOTAL "> % TOTAL </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl24 height=15>1FSLTD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num>0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=15>ABAXBANK SPA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 align=right x:num="9.0389000000000008E-3">0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: black 0.5pt solid" class=xl31 align=right x:num>100</TD></TR></TBODY></TABLE>

I hope this all makes sense.

If you ahve any questions please feel free to ask and I will endeavour to provide more detail

Thanks

Chris

 

[Andrew Poulsom]

Have you tried the macro recorder? The AddFields method applies only to row, column and page fields, not to data fields.

 

[RoryA]

Have you tried recording a macro while creating the pivot table you want? That should give you the final steps you need.

 

[GreenyMcDuff]

Thanks guys, didn't realise you could record a Pivot Table.

I have done that and can get it to work until I put a variable range in for the data field.

So it works if I create the Pivot table using this:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "[COLOR=red]Data!R6C2:R4254C12[/COLOR]").CreatePivotTable TableDestination:= _
        "'[Sales_Core_Report_Template - Pivot Test.xls]PivotTable'!R4C2", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

But not if I replace the red with this:

    FinalRow = Sheets("Data").Cells(Application.Rows.Count, 2).End(xlUp).Row
    FinalCol = Sheets("Data").Cells(20, Application.Columns.Count).End(xlToLeft).Column
    Set PRange = Sheets("Data").Cells(6, 2).Resize(FinalRow, FinalCol)
 
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= [COLOR=red]PRange[/COLOR]).CreatePivotTable TableDestination:= _
        "'[Sales_Core_Report_Template - Pivot Test.xls]PivotTable'!R4C2", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

The error I get says "The PivotTable field name is not valid"

Hope you can help

Thanks

 

[Andrew Poulsom]

Why are you using Resize(FinalRow, FinalCol) when you are starting at B6? You need to deduct 5 from FinalRow and 1 from FinalCol.

 

[GreenyMcDuff]

Very good point... and I shall answer your question with a simple yet sometimes understated response:

Because I am a muppet.

Oh and thanks btw