Forum Discussion
Solved
Finding a way to generate "report" exports (PDF) down a list of ID's
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 o...
See the attached version. It is a macro-enabled workbook so you'll have to allow macros when you open it.
See the attached version. It is a macro-enabled workbook so you'll have to allow macros when you open it.
Do I step into the macro, copy-paste into my full dataset and change cell/sheet values?
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)
- 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 SubChange
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"