Macro to produce Pivot based on a Data set

[ldashev]

Hi,

Could someone help design a macro to design a pivot based on this piece of data:

The two columns that I need in the pivot are company and amount (but need to adjust number format field to $ sign) and introduce a new field that would reflect the ratio each company represents from gross amount. I will post what I'd like the pivot to look like in the next post.

Date	ID	REf #	Amount	Company
2/27/2017	111	201701	100	IBM
2/27/2017	222	201702	100	HP
2/27/2017	333	201703	500	IBM
2/27/2017	444	201704	200	Ford
2/27/2017	555	201705	300	IBM
2/27/2017	666	201706	600	IBM
2/27/2017	777	201707	200	HP

<tbody>
</tbody>

 

[Scott T]

I should have dim gt as double instead of long
This should run without errors.

Sub pivot()
Dim lr As Long
Dim gt As Double
Dim lrg As Long
lr = Sheets("Beneficiary").Cells(Rows.Count, 1).End(xlUp).Row
Application.DisplayAlerts = False
Dim worksha As Integer
worksha = Application.Sheets.Count
For x = 1 To worksha
If Worksheets(x).Name = "BenePivot" Then
Sheets("BenePivot").Delete
Exit For
End If
Next x
Sheets.Add.Name = "BenePivot"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Beneficiary!R1C1:R" & lr & "C5", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="BenePivot!R3C1", TableName:="PivotTable5", DefaultVersion _
:=xlPivotTableVersion14
Sheets("BenePivot").Select
Cells(2, 1).Select
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Secondary Orig")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("Trxn Amount"), "Sum of Amount", xlSum
Range("B5").Select
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Sum of Amount")
.NumberFormat = "$#,##0.00"
End With


ActiveSheet.PivotTables("PivotTable5").PivotFields("Secondary Orig").AutoSort _
xlAsending, "Sum of Amount", ActiveSheet.PivotTables("PivotTable5").PivotColumnAxis. _
PivotLines(1), 1
lrg = Sheets("Date").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("BenePivot").Activate
lrp = Sheets("BenePivot").Cells(Rows.Count, 1).End(xlUp).Row
gt = Application.WorksheetFunction.Sum(Sheets("Date").Range("d2:d" & lrg))
For i = 4 To lrp - 1
Cells(i, 3) = Cells(i, 2) / gt
Cells(i, 3).NumberFormat = "0.00%"
Next i


End Sub

 

[ldashev]

And again to sort by % descending?

 

[Scott T]

Change xlAsending to xlDescending in the line

[COLOR=#333333]ActiveSheet.PivotTables("PivotTable5").PivotFields("Secondary Orig").AutoSort _
[/COLOR][COLOR=#ff0000]xlAsending[/COLOR][COLOR=#333333], "Sum of Amount", ActiveSheet.PivotTables("PivotTable5").PivotColumnAxis. _[/COLOR]