Untitled

Sub PrintMultiplePDFsFromDatabase()
    Dim ws As Worksheet
    Dim db As ListObject
    Dim pdfFileName As String
    Dim savePath As String
    Dim maxNo As Long
    Dim i As Long
    Dim newFolderPath As String
    
    
    ' Set worksheets
    Set ws = ThisWorkbook.Sheets("print")
    
    ' Set table
    Set db = ThisWorkbook.Sheets("source").ListObjects("source")
    
    ' Get the maximum value in the "No" column of the "database" table
    maxNo = Application.WorksheetFunction.Max(db.ListColumns("No").DataBodyRange)
    
    ' Define the new folder path
    newFolderPath = "X:\02 PALUPI\2024 - LANGKAH KEJAYAAN\report pdf\sn\" & ws.Range("AK3").Value & "\print"
    
    ' Create the new folder if it does not exist
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(newFolderPath) Then
        fso.CreateFolder (newFolderPath)
    End If
    
    ' Loop from 1 to maxNo
    For i = 1 To maxNo
        ' Set the value in cell A1
        ws.Range("AI6").Value = i
        
        ' Define the PDF file name and path
        pdfFileName = ws.Range("AI15").Value & " " & ws.Range("G15").Value & ".pdf"
        savePath = newFolderPath & "\" & pdfFileName
        
        ' Set the print area (adjust range as necessary)
        ws.PageSetup.PrintArea = ws.Range("A1:AG48").Address ' Adjust the print area as needed
        
        ' Print the selected area to PDF
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath, Quality:=xlQualityStandard
    Next i
    
    MsgBox maxNo & " PDF files have been created in " & ThisWorkbook.Path
End Sub