VBA for a 'Print All to PDF' Button?

[shadowsinner]

Hi all,

I'm not sure if this could be done, but does anyone know how to use a macro function to make a Print All to PDF button that is on a VLOOKUP worksheet?

I have a workbook with my grade level's student data, with each class separated onto different worksheets. One of the last worksheets is a display of all students' data pulled out of the individual class's worksheets. The very last worksheet is a formatted page that pulls data via VLOOKUP from the total student body's worksheet that allows you to view individual students' results one by one.

Currently on that last worksheet is a Print All button linked to a macro PrintAll function that allows me to print all the students' data individually, page by page. However, in an effort to save paper, I would like to have the function converted to a 'Print All to PDF' function, rather than just a Print All.

Is this possible?

Currently, this is what I'm using for the PrintAll function:

Sub PrintAll()


Dim copynumber As Long


For copynumber = 1 To 97
With ActiveSheet
.Range("M8").Value = copynumber
.PrintOut
End With
Next copynumber
End Sub


​

Does this make sense to anyone? Is this even possible in Excel? Your help is greatly appreciated.

Thank you for your help.


https://drive.google.com/open?id=1QB-6ganLr2uoexwhGT34cMoO9Ji-II67 <-- this is a screenshot of my last formatted sheet for the PrintAll function.

 

[rlv01]

Possibly something like this

Sub PrintAll()

    Dim copynumber As Long
    Dim PDFName As String, Path As String

    For copynumber = 1 To 97
        With ActiveSheet
            .Range("M8").Value = copynumber
            
            Path = CurDir
            PDFName = Path & "\StudentData" & copynumber & ".pdf"
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFName
        End With
    Next copynumber
End Sub