Excel VBA: Tự động hoá báo cáo doanh số

Sub TaoBaoCaoDoanhSo()
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim dict As Object
    Dim reportRow As Long
    Dim maxRevenue As Double
    Dim maxRow As Long
    
    Set wsData = ThisWorkbook.Sheets("Data")
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("Bao cao").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Set wsReport = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsReport.Name = "Bao cao"
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim i As Long
    For i = 2 To lastRow
        Dim nv As String
        nv = wsData.Cells(i, 2).Value
        
        If Not dict.Exists(nv) Then
            dict.Add nv, Array(0, 0)
        End If
        
        Dim arr As Variant
        arr = dict(nv)
        arr(0) = arr(0) + 1
        arr(1) = arr(1) + wsData.Cells(i, 6).Value
        dict(nv) = arr
    Next i
    
    With wsReport
        .Cells(1, 1).Value = "BAO CAO DOANH SO"
        .Cells(1, 1).Font.Size = 16
        .Cells(1, 1).Font.Bold = True
        
        .Cells(3, 1).Value = "STT"
        .Cells(3, 2).Value = "Nhan vien"
        .Cells(3, 3).Value = "So don"
        .Cells(3, 4).Value = "Tong doanh thu"
        
        .Range("A3:D3").Font.Bold = True
        .Range("A3:D3").Interior.Color = RGB(0, 102, 204)
        .Range("A3:D3").Font.Color = RGB(255, 255, 255)
    End With
    
    reportRow = 4
    maxRevenue = 0
    Dim stt As Long: stt = 1
    
    Dim key As Variant
    For Each key In dict.Keys
        wsReport.Cells(reportRow, 1).Value = stt
        wsReport.Cells(reportRow, 2).Value = key
        wsReport.Cells(reportRow, 3).Value = dict(key)(0)
        wsReport.Cells(reportRow, 4).Value = dict(key)(1)
        wsReport.Cells(reportRow, 4).NumberFormat = "#,##0"
        
        If dict(key)(1) > maxRevenue Then
            maxRevenue = dict(key)(1)
            maxRow = reportRow
        End If
        
        stt = stt + 1
        reportRow = reportRow + 1
    Next key
    
    wsReport.Range("A" & maxRow & ":D" & maxRow).Interior.Color = RGB(255, 255, 0)
    wsReport.Columns("A:D").AutoFit
    
    MsgBox "Bao cao da tao thanh cong!", vbInformation
    wsReport.Activate
End Sub

Set dict = CreateObject("Scripting.Dictionary")

wsReport.Range("A" & maxRow & ":D" & maxRow).Interior.Color = RGB(255, 255, 0)

wsReport.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & "\BaoCao.pdf"