Save Each sheet to a PDF file and rename each sheet from a list of names and other criteria.

[ob1tech77]

Hello!

I was wondering if someone can help me with this task. I'm not very good at Macros. I have a file with about 150 sheets. I want to be able to save every sheet to pdf format and rename it from a list located in the worksheet. Name of file would look like this JJones-1stGP-Speech-23-24.pdf Every sheet matches the list. It would be even better if I there is some sort of button that can be pressed and it can initiate the process. I would really appreciate your help with this task.

 

[ob1tech77]

First picture shows the sheet with the list of the names. The sheet is called caseload. The second pictures shows the sheets that I want to be save to pdf individually and the file name has to match the caseload list plus 1stGP-Speech-23-24.pdf after the name. There are 125 sheets, named Student1 to Student125.

 

[eiloken]

First picture shows the sheet with the list of the names. The sheet is called caseload. The second pictures shows the sheets that I want to be save to pdf individually and the file name has to match the caseload list plus 1stGP-Speech-23-24.pdf after the name. There are 125 sheets, named Student1 to Student125.

so i see that pdf file will be named with match student in your export sheet, but i can't see address of student cell, i need cleary picture to see it

 

[ob1tech77]

so i see that pdf file will be named with match student in your export sheet, but i can't see address of student cell, i need cleary picture to see it

The caseload sheet auto populates the forms (student1-student125) I’ll send a clearer picture.

 

[eiloken]

The caseload sheet auto populates the forms (student1-student125) I’ll send a clearer picture.

i saw that your student sheet include student name like your student sheet picture so i think we can export student sheet to "Studen" + "-1stGP-Speech-23-24.pdf". is it true? if we can export like that, i need you to show me the address of student cell in your student sheet

 

[eiloken]

i saw that your student sheet include student name like your student sheet picture so i think we can export student sheet to "Studen" + "-1stGP-Speech-23-24.pdf". is it true? if we can export like that, i need you to show me the address of student cell in your student sheet

and all student sheet in excel named as "Student #" right?

 

[ob1tech77]

i saw that your student sheet include student name like your student sheet picture so i think we can export student sheet to "Studen" + "-1stGP-Speech-23-24.pdf". is it true? if we can export like that, i need you to show me the address of student cell in your student sheet

Yes exactly that will work. Does this help? Sorry I’m not in front of my computer so I’m sending a phone screen shot.

 

[eiloken]

Yes exactly that will work. Does this help? Sorry I’m not in front of my computer so I’m sending a phone screen shot.

this code will find all sheet that has name start with "Student", replace all special characters that can be named as file and export them to pdf

Sub ExportSheetToPDF()
    Dim sh As Worksheet
    Dim cll As Range, rng As Range
    Dim xName As String
    Dim i As Integer
    Dim SpecialSymbol As Variant
    SpecialSymbol = Array("\", "/", ":", "*", "?", """", "<", ">", "|") 'special symbol that can not in the file name
    For Each sh In ThisWorkbook.Sheets
        If sh.Name Like "Student*" Then 'find sheet name start with 'Student'
            xName = sh.Cells(4, 2).Value
            For i = LBound(SpecialSymbol) To UBound(SpecialSymbol)
                If InStr(sh.Cells(4, 2).Value, SpecialSymbol(i)) > 0 Then ' replace all special symbol in student name
                    xName = Replace(sh.Cells(4, 2).Value, SpecialSymbol(i), " ")
                End If
            Next i
            sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & xName & "-1stGP-Speech-23-24.pdf" , _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False 'export to pdf
        End If
    Next sh
End Sub

 

[jolivanes]

For naming, it uses the name in Cell B2 of the student sheet. (It looks like the student name is in Cell B2)
It saves the pdf file in the same folder where the Workbook resides.
Obviously not tested.

Sub Maybe()
Dim i As Long, ws As Worksheet
Application.ScreenUpdating = False
    For i = 1 To 125
        Set ws = Worksheets("Student" & i)
            With ws
                .ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Left(ws.Cells(2, 2), 1) & Trim(Split(ws.Cells(2, 2), " ")(1)) & " 1stGP-Speech-23-24" & ".pdf"
            End With
        Set ws = Nothing
    Next i
Application.ScreenUpdating = True
End Sub

If it does not produce the right result, all you have to do is delete 125 files.
Therefore it might be better to change the 125 in the code to 10 or so.

I later posts I think that it says that the student's name is in Cell (4, 2) which is B4. If so, change the "Cells(2, 2)" in the code to "Cells(4, 2)

 

[ob1tech77]

this code will find all sheet that has name start with "Student", replace all special characters that can be named as file and export them to pdf

Sub ExportSheetToPDF()
    Dim sh As Worksheet
    Dim cll As Range, rng As Range
    Dim xName As String
    Dim i As Integer
    Dim SpecialSymbol As Variant
    SpecialSymbol = Array("\", "/", ":", "*", "?", """", "<", ">", "|") 'special symbol that can not in the file name
    For Each sh In ThisWorkbook.Sheets
        If sh.Name Like "Student*" Then 'find sheet name start with 'Student'
            xName = sh.Cells(4, 2).Value
            For i = LBound(SpecialSymbol) To UBound(SpecialSymbol)
                If InStr(sh.Cells(4, 2).Value, SpecialSymbol(i)) > 0 Then ' replace all special symbol in student name
                    xName = Replace(sh.Cells(4, 2).Value, SpecialSymbol(i), " ")
                End If
            Next i
            sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & xName & "-1stGP-Speech-23-24.pdf" , _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False 'export to pdf
        End If
    Next sh
End Sub

Amazing! This worked great. Thank you so much. You saved us a ton of work.

Let’s say I want to save the PDFs to a folder in the desktop called logs. What do I change in the code?