Some times its required to create charts on the fly and repeatedly for a report.

As always the data in report reduces or increases ( Rows) but the Column names (header) remains the same.

OrderDate Region Rep Item Units UnitCost Total
1/6/2016 East Jones Pencil 95 1.99 189.05
1/23/2016 Central Kivell Binder 50 19.99 999.50
2/9/2016 Central Jardine Pencil 36 4.99 179.64
2/26/2016 Central Gill Pen 27 19.99 539.73
3/15/2016 West Sorvino Pencil 56 2.99 167.44
4/1/2016 East Jones Binder 60 4.99 299.40
4/18/2016 Central Andrews Pencil 75 1.99 149.25
5/5/2016 Central Jardine Pencil 90 4.99 449.10
5/22/2016 West Thompson Pencil 32 1.99 63.68
6/8/2016 East Jones Binder 60 8.99 539.40

Say we want to create charts for each region and they may increase in future, say next time ‘ North’ may come.

Let’s start by creating a summary first.  This is what we need to create charts. It’s currently done by formulas to explain what we actually want, We will create this with VBA.

charts1

Attached is the code

‘—————————————————————————–

Sub MyMacro()

‘ let’s start by assigning objects the sheets to objects.

Dim SummarySheet As Worksheet
Dim SourceSheet As Worksheet

Set SourceSheet = ActiveWorkbook.Worksheets(“Data”)
‘ Lets add the Region

Dim Regions() As String
‘ lets assign the unique regions to array

ReDim Regions(0)

‘ Lets find the column names

Regions_Column = getColFromAddr(getAddress(SourceSheet, “Region”))
Unit_Column = getColFromAddr(getAddress(SourceSheet, “Units”))
Total_Column = getColFromAddr(getAddress(SourceSheet, “Total”))
‘ Assign the first element to array

Regions(0) = SourceSheet.Cells(2, Regions_Column)

‘ Lets start a loop to assign unique from column to array

‘ i starts from 3 since the element at 2 was already assigned to array.

For i = 3 To SourceSheet.UsedRange.Rows.Count

found = 0

For j = 0 To UBound(Regions())

If SourceSheet.Cells(i, Regions_Column) = Regions(j) Then
found = 1
Exit For
End If

Next j

If found = 0 Then
mybound = UBound(Regions()) + 1
ReDim Preserve Regions(mybound)
Regions(mybound) = SourceSheet.Cells(i, Regions_Column)

End If
Next i
Sheets.Add
ActiveSheet.Name = “Summary VBA”

Set SummarySheet = ActiveWorkbook.Worksheets(“Summary VBA”)

‘ Create Headers

SummarySheet.Cells(1, 1) = “Regions”
SummarySheet.Cells(1, 2) = “Units”
SummarySheet.Cells(1, 3) = “Total”

‘ lets add the regions to summary sheet

For i = 0 To UBound(Regions())

UnitSum = 0
TotalSum = 0

SummarySheet.Cells(i + 2, 1) = Regions(i)

For j = 2 To SourceSheet.UsedRange.Rows.Count

If SourceSheet.Cells(j, Regions_Column) = Regions(i) Then

UnitSum = UnitSum + SourceSheet.Cells(j, Unit_Column)
TotalSum = TotalSum + SourceSheet.Cells(j, Total_Column)

End If

Next j

SummarySheet.Cells(i + 2, 2) = UnitSum
SummarySheet.Cells(i + 2, 3) = TotalSum

Next i
End Sub

‘—————————————————————-

Here is the output.

Charts2.JPG

Here is the file with code

make-summary-vba

Tip: you can do any math operation, like average etc and add more columns

Eg. Region and then Item or Rep.

Following posts will help as they are building blocks for above code.

Column manipulation – VBA Excel

Unique Array from a column – VBA / Excel

Find a column by name – VBA Excel

Cheers

CuriousJatin

Advertisements