Sometimes we have data that have dates, and we have to create a monthly summary.

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
6/25/2016 Central Morgan Pencil 90 4.99 449.10
7/12/2016 East Howard Binder 29 1.99 57.71
7/29/2016 East Parent Binder 81 19.99 1,619.19
8/15/2016 East Jones Pencil 35 4.99 174.65
9/1/2016 Central Smith Desk 2 125.00 250.00
9/18/2016 East Jones Pen Set 16 15.99 255.84
10/5/2016 Central Morgan Binder 28 8.99 251.72
10/22/2016 East Jones Pen 64 8.99 575.36
11/8/2016 East Parent Pen 15 19.99 299.85
11/25/2016 Central Kivell Pen Set 96 4.99 479.04
12/12/2016 Central Smith Pencil 67 1.29 86.43
12/29/2016 East Parent Pen Set 74 15.99 1,183.26
1/15/2017 Central Gill Binder 46 8.99 413.54
2/1/2017 Central Smith Binder 87 15.00 1,305.0

Now the montly summary will have sum of the Total No of items sold vs Total Sale.

Month1.JPG

I would highly recommend downloading this attached file and running this code side by side  Make Monthly Charts – VBA

Let’s first start by creating a summary page.See this post on how to do it Step By Step.

Add Summary – VBA Excel

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

Sub MySummary()

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

Dim SummarySheet As Worksheet
Dim SourceSheet As Worksheet

Set SourceSheet = ActiveWorkbook.Worksheets(“Data”)
‘ Lets find the column names

Unit_Column = getColFromAddr(getAddress(SourceSheet, “Units”))
Total_Column = getColFromAddr(getAddress(SourceSheet, “Total”))

OrderDate_Column = getColFromAddr(getAddress(SourceSheet, “OrderDate”))
‘ Now we need the minimum and maximum Month
‘ Notice how we are using the max and min function of Excel and not doing it manually

mymax = Application.Max(Columns(OrderDate_Column))
mymin = Application.Min(Columns(OrderDate_Column))

Startingdate = Format(mymin, “dd-mmm-yy”)
EndDate = Format(mymax, “dd-mmm-yy”)

‘ Now we find the minimum Month – year and maximum Month – year
MinMonth = Month(Startingdate)
MinYear = Year(Startingdate)

MaxMonth = Month(EndDate)
maxYear = Year(EndDate)
‘Now we need to add the dates to summarysheet

Sheets.Add

ActiveSheet.Name = “Summary VBA”

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

nDateRow = 1
UnitsStartCol = 2
MyIndex = 1
SummarySheet.Cells(2, 1) = “Untis”
SummarySheet.Cells(2, 1).Font.Bold = True

SummarySheet.Cells(3, 1) = “Total”
SummarySheet.Cells(3, 1).Font.Bold = True

For nIndex = 0 To DateDiff(“m”, Startingdate, EndDate)

DateCheck = DateAdd(“m”, nIndex, CDate(Startingdate))
DateCheckMonth = Month(DateCheck)
DateCheckYear = Year(DateCheck)

If (DateCheckMonth >= MinMonth And DateCheckYear >= MinYear) And (DateCheckMonth <= MaxMonth And DateCheckYear <= maxYear) Then

MyIndex = MyIndex + 1
SummarySheet.Cells(nDateRow, MyIndex) = DateAdd(“m”, nIndex, CDate(Startingdate))
SummarySheet.Cells(nDateRow, MyIndex).Font.Bold = True

End If

Next nIndex
‘ Now that we have months lets start by adding Units Sold and Total

For i = 2 To SourceSheet.UsedRange.Rows.Count
‘ Let’s find the month column for this date
For j = 1 To SummarySheet.UsedRange.Columns.Count
If Year(SourceSheet.Cells(i, OrderDate_Column)) = Year(SummarySheet.Cells(1, j)) Then
If Month(SourceSheet.Cells(i, OrderDate_Column)) = Month(SummarySheet.Cells(1, j)) Then

SummarySheet.Cells(2, j) = SummarySheet.Cells(2, j) + SourceSheet.Cells(i, Unit_Column)
SummarySheet.Cells(3, j) = SummarySheet.Cells(3, j) + SourceSheet.Cells(i, Total_Column)

End If
End If

Next j

Next i

End Sub

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

Now this code creates something like this

Month2

Let’s use our another post on how to use the macro recorder to get a code for creating the chart.

See this post  Add Charts – VBA / Excel

Using macro recorder, I got this.

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

Sub Macro1()

‘ Macro1 Macro


Sheets(“Summary VBA”).Select
Range(“A1”).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range(“‘Summary VBA’!$A$1:$Y$3”)
End Sub

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

Most important line are last 2 ones, Lets use these in our chart create code

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

Sub MakeCharts()
Dim ChartSheet As Worksheet
Dim SummarySheet As Worksheet

Sheets.Add

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

ChartSheet.Name = “Charts – VBA”
LastColumn = getStrOfCol(getLastColumn(SummarySheet, 1))

myRange = “‘” & SummarySheet.Name & “‘!$A$1:$” & LastColumn & “$3”

ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range(myRange)

End Sub

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

Tip: You can create charts for all the Reps. See this Add Charts – VBA / Excel

Also, can you add Chart labels ????? Use the macro recorder to get the code.

Cheers

CuriousJatin

Advertisements