Look up from list and save multiple PDF's from list of cell value

[Brianpore]

I think this is possible but am not even sure where to start.

I have sheet 1 with a list of 50 values in column A (names of people). On sheet two I would like each each name to go into cell C2 which looks up though IF statements a bunch of stuff from sheet 3 and fills it in on sheet 2. After the lookup is activated by sheet 2 cell C2 I would want to save as or print a PDF of sheet 2 with the file being saved as the value from the C2 cell. Hopefully run a program and have it spit out 50 pdf's (one for each name on sheet 1) as right now I copy/paste/print PDF for each one which takes a lot of time.

Thanks in advance for any help/direction of where to start if this is possible.

 

[Trebor76]

H Brianpore,

Welcome to MrExcel!!

Well done setting out your data this way. So often people use separate tabs which in my humble opinion is not the way to go.

Though untested, try this (just change lines of code I've commented if need be):

Option Explicit
Sub Macro3()

    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row) 'Starts at cell A2 of 'Sheet1' and continues down Col. A to the last used (populated) row.  Change if required.
        With Sheets("Sheet2")
            .Range("C2").Value = rngMyCell
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\My Data\" & Sheets("Sheet2").Range("C2").Value & ".pdf" '<- Change the directory to where you want the PDF's saved.
        End With
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Brianpore]

H Brianpore,

Welcome to MrExcel!!

Well done setting out your data this way. So often people use separate tabs which in my humble opinion is not the way to go.

Though untested, try this (just change lines of code I've commented if need be):

Option Explicit
Sub Macro3()

    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row) 'Starts at cell A2 of 'Sheet1' and continues down Col. A to the last used (populated) row.  Change if required.
        With Sheets("Sheet2")
            .Range("C2").Value = rngMyCell
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\My Data\" & Sheets("Sheet2").Range("C2").Value & ".pdf" '<- Change the directory to where you want the PDF's saved.
        End With
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

WOW!!!! That works 100% perfect and just saved me hours and hours a month of work (in reality I have more like 200+ rows and growing every month I was doing the copy/paste/print with).

Hope it's not too much to ask one follow up question, which is very very minor in comparison. On sheet 2 I have 253 rows worth of blank lookup formulas. Depending on the name put into sheet 2 cell c2 sometimes there is only 2 or 3 rows activated where data pops up and sometimes 100 rows (2+ pdf pages). I then manually told it to print only page 1 to 1 vs printing all 5 pages based on the amount of lines that are visibly seen. If I saw it fall over to a 2nd or 3rd pdf page I would tell it to print page 1 to 2 or 1 to 3 only, not all 5. The large majority are only one page, so 2 part question, worst case is there a way to tell it to only print page 1-1 (and I'll do the couple that are larger manually) or even more advanced a way for it to only print the active pages 1-2 if the data from the IF statements goes onto a 2nd page?

Thank you again, the first set of code did 100% exactly what I was trying to accomplish

 

[Trebor76]

Hi Brian,

Thanks for the feedback and kind PM

You could set the print range for the total number of rows you have and then each time loop through the rows hiding the blank ones. There may be other solutions.

I'm about to head off for the night but someone here will be able to help I'm sure.

Regards,

Robert