Option Explicit Private Const SOURCE_SHEET As String = "Sales" Private Const REPORT_PREFIX As String = "Rep_" '========================================================= ' MAIN PROCEDURE '========================================================= Public Sub ExportSalesByRep() Dim wb As Workbook Dim wsSource As Worksheet Dim salesData As Variant Dim reps As Object Dim rep As Variant Dim wsReport As Worksheet Dim outputFolder As String On Error GoTo ErrorHandler Set wb = ThisWorkbook Set wsSource = wb.Worksheets(SOURCE_SHEET) outputFolder = GetOutputFolder(wb) salesData = GetSalesData(wsSource) Set reps = GetUniqueSalesReps(salesData) Application.ScreenUpdating = False For Each rep In reps.Keys Set wsReport = CreateReportSheet(wb, CStr(rep)) PopulateReport wsReport, salesData, CStr(rep) FormatReport wsReport ExportReportPDF wsReport, outputFolder, CStr(rep) Next rep Application.ScreenUpdating = True MsgBox reps.Count & " sales reports exported successfully.", _ vbInformation, "Export Complete" Exit Sub ErrorHandler: Application.ScreenUpdating = True MsgBox "Export failed: " & Err.Description, _ vbCritical, "Export Error" End Sub '========================================================= ' VALIDATE WORKBOOK AND READ SOURCE DATA '========================================================= Private Function GetOutputFolder(ByVal wb As Workbook) As String If Len(wb.Path) = 0 Then Err.Raise vbObjectError + 100, , _ "Save the workbook before exporting PDFs." End If GetOutputFolder = wb.Path & Application.PathSeparator End Function Private Function GetSalesData(ByVal ws As Worksheet) As Variant Dim lastRow As Long lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow < 2 Then Err.Raise vbObjectError + 101, , _ "The Sales sheet contains no data." End If GetSalesData = ws.Range("A1:D" & lastRow).Value2 End Function '========================================================= ' IDENTIFY UNIQUE SALES REPRESENTATIVES '========================================================= Private Function GetUniqueSalesReps(ByVal data As Variant) As Object Dim reps As Object Dim i As Long Dim repName As String Set reps = CreateObject("Scripting.Dictionary") reps.CompareMode = vbTextCompare For i = 2 To UBound(data, 1) If Not IsError(data(i, 2)) Then repName = Trim$(CStr(data(i, 2))) If Len(repName) > 0 Then If Not reps.Exists(repName) Then reps.Add repName, True End If End If End If Next i If reps.Count = 0 Then Err.Raise vbObjectError + 102, , _ "No sales representatives were found." End If Set GetUniqueSalesReps = reps End Function '========================================================= ' CREATE OR REUSE A REPORT WORKSHEET '========================================================= Private Function CreateReportSheet( _ ByVal wb As Workbook, _ ByVal repName As String) As Worksheet Dim ws As Worksheet Dim sheetName As String sheetName = SafeSheetName(REPORT_PREFIX & repName) On Error Resume Next Set ws = wb.Worksheets(sheetName) On Error GoTo 0 If ws Is Nothing Then Set ws = wb.Worksheets.Add( _ After:=wb.Worksheets(wb.Worksheets.Count)) ws.Name = sheetName Else ws.Cells.Clear End If Set CreateReportSheet = ws End Function '========================================================= ' POPULATE SALES DATA AND ADD TOTAL '========================================================= Private Sub PopulateReport( _ ByVal ws As Worksheet, _ ByVal data As Variant, _ ByVal repName As String) Dim i As Long Dim outputRow As Long Dim totalRow As Long ws.Range("A1:D1").Value = _ Array("Date", "Sales Rep", "Customer", "Amount") outputRow = 2 For i = 2 To UBound(data, 1) If Not IsError(data(i, 2)) Then If StrComp(Trim$(CStr(data(i, 2))), _ repName, vbTextCompare) = 0 Then ws.Cells(outputRow, 1).Resize(1, 4).Value = _ Array(data(i, 1), data(i, 2), _ data(i, 3), data(i, 4)) outputRow = outputRow + 1 End If End If Next i totalRow = outputRow + 1 ws.Cells(totalRow, 3).Value = "TOTAL" ws.Cells(totalRow, 4).Formula = _ "=SUM(D2:D" & outputRow - 1 & ")" End Sub '========================================================= ' FORMAT REPORT '========================================================= Private Sub FormatReport(ByVal ws As Worksheet) Dim lastDataRow As Long Dim totalRow As Long Dim tbl As ListObject totalRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row lastDataRow = totalRow - 2 Set tbl = ws.ListObjects.Add( _ xlSrcRange, ws.Range("A1:D" & lastDataRow), , xlYes) tbl.TableStyle = "TableStyleMedium2" With ws.Range("C" & totalRow & ":D" & totalRow) .Font.Bold = True .Interior.Color = RGB(221, 235, 247) .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeTop).Weight = xlMedium End With ws.Columns("A").NumberFormat = "dd mmm yyyy" ws.Columns("D").NumberFormat = "#,##0.00" ws.Columns("A:D").AutoFit ConfigurePrintLayout ws, totalRow End Sub '========================================================= ' CONFIGURE PDF PRINT LAYOUT '========================================================= Private Sub ConfigurePrintLayout( _ ByVal ws As Worksheet, _ ByVal totalRow As Long) With ws.PageSetup .PrintArea = ws.Range("A1:D" & totalRow).Address .Orientation = xlLandscape .PaperSize = xlPaperA4 .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .CenterHorizontally = True .PrintTitleRows = "$1:$1" .LeftMargin = Application.InchesToPoints(0.3) .RightMargin = Application.InchesToPoints(0.3) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) End With End Sub '========================================================= ' EXPORT WORKSHEET TO PDF '========================================================= Private Sub ExportReportPDF( _ ByVal ws As Worksheet, _ ByVal outputFolder As String, _ ByVal repName As String) Dim pdfPath As String pdfPath = outputFolder & _ SafeFileName(repName) & "_Sales.pdf" ws.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=pdfPath, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False End Sub '========================================================= ' SANITIZE WORKSHEET NAMES '========================================================= Private Function SafeSheetName(ByVal value As String) As String Dim invalidChars As Variant Dim ch As Variant invalidChars = Array("\", "/", ":", "*", "?", _ "[", "]") For Each ch In invalidChars value = Replace(value, CStr(ch), "_") Next ch SafeSheetName = Left$(value, 31) End Function '========================================================= ' SANITIZE PDF FILE NAMES '========================================================= Private Function SafeFileName(ByVal value As String) As String Dim invalidChars As Variant Dim ch As Variant invalidChars = Array("\", "/", ":", "*", "?", _ """", "<", ">", "|") For Each ch In invalidChars value = Replace(value, CStr(ch), "_") Next ch SafeFileName = Trim$(value) End Function