VBA Loop - Data sheet to pdf file

[rowbro]

Evening. I have a excel table (auction data sheet ) with various rows of data, and I need to put each row into another sheet (Output sheet) and then save these as a PDF with the Date, Manager and Bond, Client fields as the filename.

I do this manually by changing the "Lookup Current" number in the input sheet, and there are vlookups for the respective rows of data in the Output sheet.

However, I would like to automate this process so that I can loop through the lookup range in the input sheet, saving each one as a PDF as it loops through.

Can someone assist with some VBA for this? My effort falls far short.

Sub TestBN()
'
' TestBN Macro
'

'
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("C5").Select
    Sheets("BrokersNote").Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "/Note_Automation.pdf", Quality _
        :=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Sheets("Input").Select
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("C8").Select
    Sheets("BrokersNote").Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "/Note_Automation2.pdf", Quality _
        :=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Sheets("Input").Select
    Range("A1").Select
End Sub

 

[John_w]

Try this macro, although your Output sheet doesn't show the Client field in its own cell, so I've decided to use cell E21 so that the full PDF file name can be constructed.

Public Sub Create_PDFs()

    Dim inputSheet As Worksheet, outputSheet As Worksheet
    Dim folder As String, PDFfile As String
    Dim i As Long
    
    With ActiveWorkbook
        Set outputSheet = .Worksheets("Output")
        Set inputSheet = .Worksheets("Input")
        folder = .Path & "\"
    End With
    
    With inputSheet
        For i = .Range("C5").Value To .Range("C6").Value
            .Range("C4").Value = i
            PDFfile = Format(outputSheet.Range("B10").Value, "YYYY-MM-DD") & " " & outputSheet.Range("B12").Value & " " & outputSheet.Range("E22").Value & " " & outputSheet.Range("E21").Value & ".pdf"
            outputSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=folder & PDFfile, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next
    End With
        
End Sub