VBA to conditionally export sheets to pdf files (loop dropdown list & sheets)

maldo81

New Member
Joined
Aug 13, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Every month I have a dashboard that needs to have a pdf created for each person provided on a drop down list. I created a code that loops through that drop down list and creates a pdf for each person. One of my bosses asked me to add pages to that pdf based on if they meet a certain condition. I created a version of that page on a different worksheet and I want the loop to go through each person on the dropdown list but also attach the other worksheets to the end of that same pdf if they meet the conditions to have that worksheet be attached.

Here is a simplistic version of that excel sheet

1628874564544.png


So the VBA should loop through the drop down list, check to see if conditions on bottom are met and should print the original sheet and the sheets that meet the conditions.


1628874594262.png


I have a print area set in all the sheets through page layout, as long as it prints the sheet it should just catch that print area, at least that is what it has been doing so far.

1628875085267.png


That's the tab that where it loops through the names to reload the original sheet each time it loops through to export, also column two is used in the naming convention

VBA Code:
Sub VenA()
  c00 = "File Path" 'Just change the path
  ar = Sheets("People").ListObjects(1).DataBodyRange
  lm = Format(DateAdd("m", -1, Date), "yyyymm")
  With Sheets("Original")
    For j = 1 To UBound(ar)
      .Range("E3") = ar(j, 1)
      .ExportAsFixedFormat 0, c00 & "Report_" & lm & "_" & ar(j, 2) & ".pdf"
    Next j
  End With
End Sub

So I need to add to that loop for it to check that range in the original sheet each time and if the condition says yes then attach the corresponding worksheet to the exported pdf.

Thank you in advance.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,143,619
Messages
5,719,772
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top