VBA script to generate pdf

[rohitsekar]

Hi All,

I have a Excel file with column named "Customer ID" there are 800 diff customers . I want to filter them one by one and save them as PDF in a specified location and file name should be customer id + country column. Is there way to do this in VBA . I am pretty new to VBA and it is quite confusing. can anyone help me?

Thank you

 

[John_w]

Try this macro, which assumes the Customer IDs are in column A and the Countries are in column B.

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 A) and associated Country (column B), keyed on Customer ID
        
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            CustomerIDsDict(.Cells(r, "A").Value) = .Cells(r, "B").Value
        Next
        
        'For each unique Customer ID
        
        For Each CustomerID In CustomerIDsDict.keys
            
            'AutoFilter on column A (Field:=1) with this Customer ID
            
            .UsedRange.AutoFilter Field:=1, 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