Option Explicit Sub ExportSalesByRep() Dim wb As Workbook Dim wsSource As Worksheet Dim wsReport As Worksheet Dim reps As Object Dim rep As Variant Dim lastRow As Long Dim r As Long, outputRow As Long Dim totalRow As Long Dim reportName As String Dim pdfPath As String Dim pdfFile As String Dim tbl As ListObject Dim dataRange As Range Dim oldScreenUpdating As Boolean Dim oldDisplayAlerts As Boolean Dim successCount As Long Dim errMessage As String On Error GoTo ErrorHandler Set wb = ThisWorkbook Set wsSource = wb.Worksheets("Sales") If Len(wb.Path) = 0 Then MsgBox "Please save the workbook first.", vbExclamation Exit Sub End If oldScreenUpdating = Application.ScreenUpdating oldDisplayAlerts = Application.DisplayAlerts Application.ScreenUpdating = False Application.DisplayAlerts = False pdfPath = wb.Path & Application.PathSeparator lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row If lastRow < 2 Then MsgBox "No sales data found.", vbInformation GoTo CleanUp End If ' Collect unique sales representatives Set reps = CreateObject("Scripting.Dictionary") reps.CompareMode = vbTextCompare For r = 2 To lastRow If Trim(CStr(wsSource.Cells(r, "B").Value)) <> "" Then reps(Trim(CStr(wsSource.Cells(r, "B").Value))) = True End If Next r ' Create one report per sales representative For Each rep In reps.Keys reportName = SafeSheetName(CStr(rep)) ' Avoid overwriting an existing worksheet reportName = UniqueSheetName(wb, reportName) Set wsReport = wb.Worksheets.Add( _ After:=wb.Worksheets(wb.Worksheets.Count)) wsReport.Name = reportName ' Copy column headers wsReport.Range("A1:D1").Value = _ wsSource.Range("A1:D1").Value outputRow = 2 ' Copy sales belonging to this representative For r = 2 To lastRow If StrComp(Trim(CStr(wsSource.Cells(r, "B").Value)), _ CStr(rep), vbTextCompare) = 0 Then wsReport.Cells(outputRow, "A").Resize(1, 4).Value = _ wsSource.Cells(r, "A").Resize(1, 4).Value outputRow = outputRow + 1 End If Next r ' Create a formatted Excel table Set dataRange = wsReport.Range("A1:D" & outputRow - 1) Set tbl = wsReport.ListObjects.Add( _ xlSrcRange, dataRange, , xlYes) tbl.TableStyle = "TableStyleMedium2" ' Add total below the table totalRow = outputRow + 1 wsReport.Cells(totalRow, "C").Value = "TOTAL SALES" wsReport.Cells(totalRow, "D").Formula = _ "=SUM(D2:D" & outputRow - 1 & ")" With wsReport.Range("C" & totalRow & ":D" & totalRow) .Font.Bold = True .Interior.Color = RGB(220, 230, 241) .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeTop).Weight = xlMedium End With ' Format columns wsReport.Columns("A").NumberFormat = "dd mmm yyyy" wsReport.Columns("D").NumberFormat = "#,##0.00" wsReport.Columns("A:D").AutoFit ' Improve readability wsReport.Columns("A").ColumnWidth = _ Application.Max(wsReport.Columns("A").ColumnWidth, 15) wsReport.Columns("B").ColumnWidth = _ Application.Max(wsReport.Columns("B").ColumnWidth, 18) wsReport.Columns("C").ColumnWidth = _ Application.Max(wsReport.Columns("C").ColumnWidth, 20) ' Configure PDF page layout With wsReport.PageSetup .Orientation = xlLandscape .PaperSize = xlPaperA4 .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintArea = wsReport.Range( _ "A1:D" & totalRow).Address .PrintTitleRows = "$1:$1" .CenterHeader = "Sales Report - " & CStr(rep) .CenterFooter = "Page &P of &N" End With ' Export PDF pdfFile = pdfPath & _ SafeFileName(CStr(rep)) & "_Sales_Report.pdf" wsReport.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=pdfFile, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False successCount = successCount + 1 Next rep CleanUp: Application.ScreenUpdating = oldScreenUpdating Application.DisplayAlerts = oldDisplayAlerts If successCount > 0 Then MsgBox successCount & _ " sales reports created and exported to:" & _ vbCrLf & pdfPath, vbInformation End If Exit Sub ErrorHandler: errMessage = Err.Description Application.ScreenUpdating = oldScreenUpdating Application.DisplayAlerts = oldDisplayAlerts MsgBox "Error: " & errMessage, vbCritical End Sub Function SafeSheetName(ByVal txt As String) As String Dim invalidChars As Variant Dim ch As Variant invalidChars = Array("/", "\", "?", "*", "[", "]", ":") For Each ch In invalidChars txt = Replace(txt, ch, "_") Next ch txt = Replace(txt, "'", "_") txt = Trim(txt) If Len(txt) = 0 Then txt = "SalesRep" SafeSheetName = Left(txt, 31) End Function Function UniqueSheetName(wb As Workbook, _ ByVal baseName As String) As String Dim candidate As String Dim counter As Long candidate = baseName counter = 1 Do While SheetExists(wb, candidate) counter = counter + 1 candidate = Left(baseName, 31 - _ Len("_" & counter)) & "_" & counter Loop UniqueSheetName = candidate End Function Function SheetExists(wb As Workbook, _ ByVal sheetName As String) As Boolean Dim ws As Worksheet For Each ws In wb.Worksheets If StrComp(ws.Name, sheetName, vbTextCompare) = 0 Then SheetExists = True Exit Function End If Next ws End Function Function SafeFileName(ByVal txt As String) As String Dim invalidChars As Variant Dim ch As Variant invalidChars = Array("\", "/", ":", "*", "?", _ """", "<", ">", "|") For Each ch In invalidChars txt = Replace(txt, ch, "_") Next ch txt = Trim(txt) If Len(txt) = 0 Then txt = "SalesRep" SafeFileName = txt End Function