Summarize data using Excel VBA

Question

I am a beginner to VBA. On sheet one I have data formatted like this:

SHEET 1

What I want to do is use VBA to spit out the following graph which dynamically populates the region depending on how many it finds:

SHEET 2

This is my first bit of VBA so I am struggling a bit. This is my idea of how to approach this problem:

My idea was to scroll down the string in my data in sheet1 col A and determine if it's a date we have seen before or not:

Public Sub Test()

ActiveSheet.Range("Sheet1!A1:A5000").AdvancedFilter Action:=xlFilterCopy, CopyToRange.Range("Sheet2!A1"), Unique:=True

End Sub

Questions

1. Is this flow diagram taking the right approach?

2. If so, how do I implement this kind of "Is this unique, if so do this, if not do this" kind of setup.

How can I start this code so I have something to build on?

This is what I have so far: https://gist.githubusercontent.com/employ/af67485b8acddce419a2/raw/6dda3bb1841517731867baec56a0bf2ecf7733a7/gistfile1.txt

Answer 1

For different approach please see below:

Sheet 1 layout (Source):

Sheet 2 Layout (Target):

Sub SalesRegion()
Dim ws1, ws2 As Worksheet
Dim wb As Workbook
Dim ws1LastRow, ws2LastRow, salesVal  As Long
Dim destFind, dateFind As Range
Dim destStr As String
Dim dateStr As Date
Dim targetCol, targetRow As Long


Set wb = ActiveWorkbook '<- Your workbook
Set ws1 = wb.Sheets("Sheet1")  '<- Your source worksheet
Set ws2 = wb.Sheets("Sheet2") '<- Your destination worksheet

ws1LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To ws1LastRow
destStr = ws1.Range("C" & i).Value
dateStr = ws1.Range("A" & i).Value
salesVal = ws1.Range("B" & i).Value

With ws2.Rows("1:1") '<- row on destination sheet which contains countries
    Set destFind = .Find(What:=destStr, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, _
        MatchCase:=False)
            If Not destFind Is Nothing Then
                targetCol = destFind.Column
                With ws2.Columns("A:A") '<- Column on destination sheet which contains months
                'You may need to adjust date format below depending on your regional settings
                Set dateFind = .Find(What:=Format(ws1.Range("A" & i).Value, "MMM-yy"), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
                        If Not dateFind Is Nothing Then
                            targetRow = dateFind.Row
                            ws2.Cells(targetRow, targetCol).Value = ws2.Cells(targetRow, targetCol).Value + salesVal
                        Else
                            ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = dateStr
                            targetRow = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
                            ws2.Cells(targetRow, targetCol).Value = salesVal

                        End If
                End With
            Else

            ws2.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Value = destStr
            targetCol = ws2.Cells(1, Columns.Count).End(xlToLeft).Column

            With ws2.Columns("A:A") '<- Column on destination sheet which contains months
                'You may need to adjust date format below depending on your regional settings
            Set dateFind = .Find(What:=Format(ws1.Range("A" & i).Value, "MMM-yy"), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
                    If Not dateFind Is Nothing Then
                        targetRow = dateFind.Row
                        ws2.Cells(targetRow, targetCol).Value = ws2.Cells(targetRow, targetCol).Value + salesVal

                        Else
                            ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = dateStr
                            targetRow = ws2.Range("A" & Rows.Count).End(xlUp).Row
                            ws2.Cells(targetRow, targetCol).Value = salesVal

                    End If
            End With
            End If
End With
Next
End Sub