PRINT TO PDF VBA

visheshkotha 

I have reviewed the file you provided. It seems like you have multiple sheets containing data for different branches and ROs, with the main data being fetched from the "HRM" tab.

To address your requirement, we will need to adjust the VBA code to properly loop through the data and generate PDFs based on either RO or certain DP codes.

For the first code you provided, we will modify it to use the data from the "HRM" tab to fetch branch details. We will also add functionality to filter based on either RO or specific DP codes.

Here is the modified code:

Vba  code is untested, please backup your file.

Sub ExportProfilesToPDF()

    Dim wsProfile As Worksheet
    Dim wsHRM As Worksheet
    Dim lastRow As Long
    Dim branchName As String
    Dim dpCode As String
    Dim savePath As String
    Dim filterRO As String
    Dim filterDP As String
    
    ' Define the main profile sheet
    Set wsProfile = ThisWorkbook.Sheets("ProfileSheet")
    Set wsHRM = ThisWorkbook.Sheets("HRM")
    
    ' Define filter values (RO Name or DP Code)
    filterRO = "RO Name" ' Change this to the desired RO Name or leave as "RO Name" to export all ROs
    filterDP = "DP Code" ' Change this to the desired DP Code or leave as "DP Code" to export all branches
    
    ' Loop through each row in the HRM sheet
    lastRow = wsHRM.Cells(wsHRM.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow ' Assuming row 1 is header
        If (filterRO = "RO Name" Or wsHRM.Cells(i, "A").Value = filterRO) And (filterDP = "DP Code" Or wsHRM.Cells(i, "B").Value = filterDP) Then
            branchName = wsHRM.Cells(i, "C").Value ' Assuming branch name is in column C
            dpCode = wsHRM.Cells(i, "B").Value ' Assuming DP code is in column B
            savePath = "Your\Desired\Save\Path\" ' Change this to your desired save path
            
            ' Print the profile sheet to PDF
            wsProfile.ExportAsFixedFormat Type:=xlTypePDF, _
                                          Filename:=savePath & dpCode & " - " & branchName & ".pdf", _
                                          Quality:=xlQualityStandard, _
                                          IncludeDocProperties:=True, _
                                          IgnorePrintAreas:=False, _
                                          OpenAfterPublish:=False
        End If
    Next i
    
    MsgBox "PDFs have been created successfully.", vbInformation

End Sub

For the second code you provided, it seems like you are already generating PDFs based on RO, DP Code, and branch. If you want to filter based on RO or specific DP codes, you can modify the filter values accordingly before running the macro.

The second code can be modified, to meet your requirements.

Let us adjust it to include filtering based on either RO or specific DP codes.

Here is the modified code:

Vba  code is untested, please backup your file.

Public Sub Create_PDFs()

    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim branch As Range
    Dim RO As Range
    Dim filterRO As String
    Dim filterDP As String
    
    destinationFolder = "C:\Inward" ' Change this to your desired folder
    'destinationFolder = "C:\path\to\folder\" 'Or specific folder
    
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
    
    ' Cell containing data validation in-cell dropdown
    Set RO = Worksheets("format").Range("G3")
    Set dataValidationCell = Worksheets("format").Range("A3")
    Set branch = Worksheets("format").Range("B3")
    
    ' Source of data validation list
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
    
    ' Define filter values (RO Name or DP Code)
    filterRO = "RO Name" ' Change this to the desired RO Name or leave as "RO Name" to export all ROs
    filterDP = "DP Code" ' Change this to the desired DP Code or leave as "DP Code" to export all branches
    
    ' Create PDF for each data validation value
    For Each dvValueCell In dataValidationListSource
        If (filterRO = "RO Name" Or RO.Value = filterRO) And (filterDP = "DP Code" Or branch.Value = filterDP) Then
            With Worksheets("format").Range("A1:T65") ' Adjust the range as per your requirement
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & RO.Value & " - " & dvValueCell.Value & " - " & branch.Value & ".pdf", _
                                     Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            End With
        End If
    Next

End Sub

This code will generate PDFs based on the filter values you set for RO and DP codes. If the filter values are set to "RO Name" or "DP Code", it will export all ROs or all branches respectively. Otherwise, it will export PDFs only for the specified RO or DP code.

Make sure to adjust the file paths and ranges as per your actual workbook structure.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.