CODEWITHSUNDEEP
excelvba
Israel Mello
Israel Mello

Reputation: 11

Create all loops in one pdf

I have this code that creates a pdf through each row (it has name, employee id number and if an employer has clearance or not to trade), the problem is that it generates one pdf with such information for each row( in this case for each employee), but to be easier for me would be easier if I had everything in just one pdf. Does anyone have any idea how I can change the code to generate everything in one pdf?

'Copyrighted of geedrecorder
Sub ExportingPDF()

'Defining worksheets
Dim detailsSheet As Worksheet
Dim reportSheet As Worksheet

Set reportSheet = ActiveWorkbook.Sheets("Design")
Set detailsSheet = ActiveWorkbook.Sheets("Mark")


'Looping the through each row
For i = 2 To 20

'Assigning values
Sname = detailsSheet.Cells(i, 1)
Spuid = detailsSheet.Cells(i, 2)
Srestriction = detailsSheet.Cells(i, 3)

'Generating the output
reportSheet.Cells(2, 2).Value = Sname
reportSheet.Cells(3, 2).Value = Spuid
reportSheet.Cells(4, 2).Value = Srestriction


'Save the PDF file
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\alyssa\Documents\Zoom\" & Sname, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False


Next i


End Sub

trying to generate one pdf with all the loops instead of one single pdf for each loop

Upvotes: 1

Views: 68

Answers (1)

FunThomas
FunThomas

Reputation: 29146

That's fairly simple. Use your loop counter as variable row (or column) counter to place all the data into reportSheet before exporting it.

If you want to have the date on top of each other, you need to multiply your i by 3 to calculate the row (because you need three rows per iteration). If you want to have an empty row between each block, just replace the 3 by 4.

reportSheet.Cells(i*3-1, 2).Value = Sname
reportSheet.Cells(i*3-0, 2).Value = Spuid
reportSheet.Cells(i*3+1, 2).Value = Srestriction

If you want to have the date next to each other, use i as column number

reportSheet.Cells(2, i+1).Value = Sname
reportSheet.Cells(3, i+1).Value = Spuid
reportSheet.Cells(4, i+1).Value = Srestriction

Now all you have to do is to move the Export-command out of the loop. And you need to make up your mind how you want to name the export file - currently it get's the name of the employee (Sname) but as you are exporting multiple employees at once, you probably want a different name.

Just a remark: If your list of employees may change, maybe it's a good idea not to hardcode the 20 as last row. I adapted your code to be flexible:

'Looping through each row
Dim i As Long, lastRow As Long
lastRow = detailsSheet.Cells(detailsSheet.Rows.Count, "A").End(xlUp).row
For i = 2 To lastRow
    'Assigning values
    Sname = detailsSheet.Cells(i, 1)
    Spuid = detailsSheet.Cells(i, 2)
    Srestriction = detailsSheet.Cells(i, 3)
    
    'Generating the output
    reportSheet.Cells(i * 3 - 1, 2).Value = Sname
    reportSheet.Cells(i * 3 + 0, 2).Value = Spuid
    reportSheet.Cells(i * 3 + 1, 2).Value = Srestriction
Next i

'Save the PDF file
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\alyssa\Documents\Zoom\" & "EmployeeList", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

And last but not least: Use Option Explicit and declare all your variables!

Upvotes: 1

Related Questions