Nov 21 2021 09:54 AM
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.
Nov 21 2021 10:24 AM
SolutionSee the attached version. It is a macro-enabled workbook so you'll have to allow macros when you open it.
Nov 21 2021 10:30 AM
Nov 21 2021 10:53 AM
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)
Nov 22 2021 10:34 AM
Nov 22 2021 12:07 PM
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"
Nov 22 2021 01:15 PM
Nov 21 2021 10:24 AM
SolutionSee the attached version. It is a macro-enabled workbook so you'll have to allow macros when you open it.