Autofilter to include Headers

[rohitsekar]

Hi All,

I have excel file with templates which looks something like this.

I am trying to filter down rows based on customer ID column and create PDF . I am using below VBA code to do the job.

Public Sub Create_PDFs()

    Dim CustomerIDsDict As Object, CustomerID As Variant
    Dim r As Long
    Dim currentAutoFilterMode As Boolean
    
    Set CustomerIDsDict = CreateObject("Scripting.Dictionary")
    
    'The code looks at data on the active sheet
    
    With ActiveSheet
    
        'Save current UI autofilter mode
        
        currentAutoFilterMode = .AutoFilterMode
        
        If currentAutoFilterMode Then .AutoFilter.ShowAllData
       
        'Create dictionary containing unique Customer IDs (column B) and associated Country (column B), keyed on Customer ID
        
        For r = 5 To .Cells(.Rows.Count, "B").End(xlUp).Row
            CustomerIDsDict(.Cells(r, "B").Value) = .Cells(r, "C").Value
        Next
        
        'For each unique Customer ID
        
        For Each CustomerID In CustomerIDsDict.keys
            
            'AutoFilter on column B (Field:=2) with this Customer ID
            
            .UsedRange.AutoFilter Field:=2, Criteria1:=CustomerID
        
            'Save filtered data as PDF file "<Customer ID> <Country>.pdf" in same folder as this workbook
            
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & CustomerID & " " & CustomerIDsDict(CustomerID) & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
                    
        Next
    
        'Restore previous autofilter, if any
    
        If currentAutoFilterMode Then
            .AutoFilter.ShowAllData
        Else
            .AutoFilterMode = False
        End If
        
    End With
    
End Sub

It filters down based on customer id correctly and creating PDF's. but the headers and footers are missing in the PDF any idea how to include those. i guess there is something wrong with this particular line --> .UsedRange.AutoFilter Field:=2, Criteria1:=CustomerID. can anyone help me? This how PDF look like.