Finding a way to generate "report" exports (PDF) down a list of ID's

[mydataismeaningless]

So I've provided dummy data to help explain my case. My real data has 200+ rows of employee salary info.

There are two sheets: "Report" and "Data"

The report values are populated by VLOOKUP based on 'employee id' (Report!B2).

GOAL: Generate a PDF export of the 'Report' sheet for each row in my employee dataset.

I'm trying to figure out how to bridge the gap between manually generating the report for each row (by typing the ID to lookup the other values) then exporting to PDF, and having it done automatically down the list of employees. 

Is there a way I can do this? A solution to this dummy data would help so much.  

[#2 · Accepted solution]

@mydataismeaningless 

See the attached version. It is a macro-enabled workbook so you'll have to allow macros when you open it.

[#3]

Do I step into the macro, copy-paste into my full dataset and change cell/sheet values?

[#4]

@mydataismeaningless

Open both your 'real' workbook and the workbook that I posted.

Press Alt+F11 to activate the Visual Basic Editor.

Drag Module1 from the latter to your real workbook. This copies the module with the code.

Double-click Module1 under your workbook, and edit the references to sheets and ranges as needed.

Then switch back to Excel and save your workbook as a macro-enabled workbook (.xlsm)

[#5]

Thanks so much!! so helpful.

Can I ask about one more related issue? I'm trying to add the full name as well.
The ID is pulled from cell D4.
I have an xlookup pulling firstname&" "&lastname in the report's cell B4. How do I get the value from B4 (John Smith) to end up in each file name? example: 100321_John Smith

I can't find the correct code to do this. I either end up with reports with blank names, or filenames with only the last name.

My Macro:

Sub OGExport2PDF()
Dim wshReport As Worksheet
Dim wshData As Worksheet
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngID As Long
Dim strPath As String
Application.ScreenUpdating = False
Set wshReport = Worksheets("Total Comp & Benefits Statement")
Set wshData = Worksheets("Salary and Benefits")
strPath = ThisWorkbook.Path & Application.PathSeparator
lngLastRow = wshData.Range("D" & wshData.Rows.Count).End(xlUp).Row
For lngRow = 3 To lngLastRow
lngID = wshData.Range("D" & lngRow).Value
wshReport.Range("D4").Value = lngID
wshReport.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & lngID & "_" & ".pdf"
Next lngRow
Application.ScreenUpdating = True
End Sub

[#6]

@mydataismeaningless

Change

    wshReport.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & lngID & "_" & ".pdf"

to

        Dim strFullName As String
        strFullName = wshReport.Range("B4").Value
        wshReport.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strPath & lngID & "_" & strFullName & ".pdf"

[#7]

Thank you so much! I couldn't figure this it out for the life of me.