Unique values

[Kaizer]

Hey guys,

Here is the story:
In Sheets("Working") I have a little DBase with Actual and Budget sales of different Products in three Regions. All sales are either Cash or Non Cash. Sales are made by Entities.

What I would like to do is to make a dynamic report, based on selected parameters on a Form, on Sheets("OutPut"). The Report should contain Entities beneith each region with Actual and Budget sales figures next to each other.

I understand that I should get three arrays, one for each region, and fill them with unique (not duplicate) entities and values. Then I need to fill the range on "OutPut" sheet with values of the Array. But I am struggling with the code. Can you help?

I attach the file with some work that I was able to accomplish so far. Manully, I made the report on "OutPut" sheet for better understanding what I want at the end.

Thanks a lot.

[byundt]

The functionality you describe is provided by the PivotTables feature (available on the Data toolbar). Here is a macro that will create one:


Sub CreatePivotTable()
Dim rg As Range
Set rg = Worksheets("Working").Cells(1, 1).CurrentRegion
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=rg).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
.AddFields RowFields:=Array("Region", "Entity", "Data")
.PivotFields("Cash sale").Orientation = xlDataField
'.PivotFields("Cash sale").Position = 1
.PivotFields("Non-cash sale").Orientation = xlDataField
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
ActiveSheet.Name = "OutPut"
Range("E3").FormulaR1C1 = "Budget"
Range("E4").FormulaR1C1 = _
"=IF(RC[-4]&RC[-3]<>"""",SUMPRODUCT((Working!R2C[-4]:R65536C[-4]=RC[-4])*(Working!R2C[-3]:R65536C[-3]=RC[-3])*Working!R2C:R65536C),"""")"
Range("E4").AutoFill Destination:=Range(Cells(4, 4), Cells(65536, 4).End(xlUp).Offset(-2, 0)).Offset(0, 1)
End Sub

Brad

[Kaizer]

Brad,

Thank you. It helped.