Adding Pivot Table Fields on VBA

[CptnDiego]

Hello all,

I am new to VBA and I have been trying to generate a Pivot table from the following data base with no luck yet.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Fund Code[/TD]
[TD]Fund Compartment[/TD]
[TD]Number of Units[/TD]
[TD]Value of Unit[/TD]
[TD]Value of Fund[/TD]
[/TR]
[TR]
[TD]5/1/18[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]50[/TD]
[TD]10[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]5/1/18[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]60[/TD]
[TD]9[/TD]
[TD]540[/TD]
[/TR]
[TR]
[TD]4/1/18[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]51[/TD]
[TD]10[/TD]
[TD]510[/TD]
[/TR]
[TR]
[TD]4/1/18[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]59[/TD]
[TD]9[/TD]
[TD]531[/TD]
[/TR]
[TR]
[TD]3/1/18[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]52[/TD]
[TD]9[/TD]
[TD]468[/TD]
[/TR]
[TR]
[TD]3/1/18[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]60[/TD]
[TD]8[/TD]
[TD]480[/TD]
[/TR]
[TR]
[TD]5/1/18[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]200[/TD]
[TD]5[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]5/1/18[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]150[/TD]
[TD]6[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD]5/1/18[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]75[/TD]
[TD]750[/TD]
[/TR]
[TR]
[TD](...)[/TD]
[TD](...)[/TD]
[TD](...)[/TD]
[TD](...)[/TD]
[TD](...)[/TD]
[TD](...)[/TD]
[/TR]
</tbody>[/TABLE]

Note that Value of Unit = Value of Fund / Number of Units

In my pivot table, I would like variables "Number of Units" and "Value of Fund" to be the sum of all compartments daily which I have already done. And, the tricky part, I would like for "Value of Unit" to show the "Value of the Unit" for the biggest compartment in terms of "Value of the fund" (highlighted in red above). For my example, the result pivot table would look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Fund Code[/TD]
[TD]Fund Compartment[/TD]
[TD]Number of Units[/TD]
[TD]Value of Unit[/TD]
[TD]Value of Fund[/TD]
[/TR]
[TR]
[TD]5/1/18[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]110[/TD]
[TD]9[/TD]
[TD]1040[/TD]
[/TR]
[TR]
[TD]4/1/18[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]110[/TD]
[TD]9[/TD]
[TD]1041[/TD]
[/TR]
[TR]
[TD]3/1/18[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]112[/TD]
[TD]8[/TD]
[TD]948[/TD]
[/TR]
[TR]
[TD]5/1/18[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]360[/TD]
[TD]5[/TD]
[TD]2650[/TD]
[/TR]
[TR]
[TD](...)[/TD]
[TD](...)[/TD]
[TD](...)[/TD]
[TD](...)[/TD]
[TD](...)[/TD]
[TD](...)[/TD]
[/TR]
</tbody>[/TABLE]

My code is as follows:

'Tabla Dinámica: Numero de Unidades, Valor Unidad, Valor Fondo, Número de Inversionistas'
Dim FuenteSht As Worksheet, DestinoSht As Worksheet, DatosFuente As Range, PvtCache As PivotCache, Pvt As PivotTable, DestinoFinal As String, DatosFinal As String, Fecha As String, Sum_NúmUnidades As String, Máx_ValorUnidad As String, Sum_ValorFondo As String, Sum_NúmInv As String, CódigoFondo As String
            
            'Set Source Data'
            Set FuenteSht = Sheets("BaseTotal")
            Set DatosFuente = Sheets("BaseTotal").Range(Cells(5, 2), Cells(LastRow, 19))
            DatosFinal = FuenteSht.Name & "!" & DatosFuente.Address(ReferenceStyle:=xlR1C1)
                
            'Set pivot table sheet and range'
            Set DestinoSht = Sheets("Ajuste_Compartimientos")
            DestinoFinal = DestinoSht.Name & "!" & DestinoSht.Range("D2").Address(ReferenceStyle:=xlR1C1)
                    
            'Make sure each column has a header'
            If WorksheetFunction.CountBlank(DatosFuente.Rows(1)) > 0 Then
                MsgBox "Una de las columnas no tiene encabezado. Por favor Arreglar en la hoja BaseTotal y volver a correr"
                Exit Sub
            End If
                    
            'Set pivot cache from source data'
            FuenteSht.Activate
                Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DatosFinal)
                
            'Create pivot table from pivot cache
            DestinoSht.Activate
                Set Pvt = PvtCache.CreatePivotTable(TableDestination:=DestinoSht.Cells(2, 4), TableName:="Tabla Dinámica")
                    
            'Add fields to pivot table'
            Set Pvt = ActiveSheet.PivotTables("Tabla Dinámica")
                Pvt.PivotFields("Fecha corte").Orientation = xlRowField
                Pvt.PivotFields("Fecha corte").DataRange.Sort Order1:=xlDescending, Type:=xlSortLabels
                Pvt.PivotFields("Cód. Negocio").Orientation = xlPageField
            Sum_NúmUnidades = "Suma Número de Unidades"
                Pvt.AddDataField Pvt.PivotFields("Núm. unidades"), Sum_NúmUnidades, xlSum
            Máx_ValorUnidad = "Máx Valor Unidad"
                Pvt.AddDataField Pvt.PivotFields("Valor unidad para las operaciones del día t"), Máx_ValorUnidad, xlMax
            Sum_ValorFondo = "Suma Valor Fondo"
                Pvt.AddDataField Pvt.PivotFields("Valor fondo al cierre del día t"), Sum_ValorFondo, xlSum
            Sum_NúmInv = "Suma Número Inversionistas"
                Pvt.AddDataField Pvt.PivotFields("Núm. Invers."), Sum_NúmInv, xlSum
                    
            'Choose Fund'
            FuenteSht.Activate
                CódigoFondo = Range("F3")
            DestinoSht.Activate
                ActiveSheet.PivotTables("Tabla Dinámica").PivotFields("Cód. Negocio").CurrentPage = CódigoFondo
                
            'Refresh pivot table'
            DestinoSht.PivotTables("Tabla Dinámica").RefreshTable
                
        'Copy pivot table values an paste in another sheet'
        Dim TablaDinámica As Range
        Set TablaDinámica = DestinoSht.Range(Cells(5, 4), Cells(5, 8).End(xlDown).Offset(-1))
        TablaDinámica.Copy
        Sheets("Fondo_Calificado").Activate
        Range("C15").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
       
        'Delete pivot table'
        DestinoSht.PivotTables("Tabla Dinámica").TableRange2.Clear

For the moment I´m using the maximum "Value of Unit" as the filtered "Value of Unit". However I would like to filter the "Value of Unit" corresponding to the biggest "Value of Fund" for each day, if this is possible. I have tried various approaches involving calculated pivot fields but have failed.

I hope I have been clear enough on my intentions.

Thanks in advance for your time and patience. Any help is greatly appreciated.

Diego