Create PDFs from Mutiple/Variable Sheets

[nick_w79]

Hello,

I have workbook with several sheets.

Five of the sheets have names that are always the same:-

Cover
Certificate
Code notes
Apportionment
_1

The remaining sheets are variable in number and name,apart from the last two characters, which all end with an underscore and anumber, starting at 2 and increasing by one for each additional sheet (e.g._2).

What I am trying to achieve is to export one PDF for eachof the “variable” sheets, but includes the contents of the fixed sheets, onepage per sheet. The filename should be the same as the relevant “variable”sheet.

For example, if the workbook started with the followingsheets:-

Cover, Certificate, Code notes, Apportionment, _1, T999999_2,T999999_3, T999999_4


I would want PDFs as follows:-

T999999_2.pdf
Page 1 Contentsof sheet “Cover”
Page 2 Contentsof sheet “Certificate”
Page 3 Contentsof sheet “T999999_2”
Page 3 Contentsof sheet “Code notes”

T999999_3.pdf
Page 1 Contentsof sheet “Cover”
Page 2 Contentsof sheet “Certificate”
Page 3 Contentsof sheet “T999999_3”
Page 3 Contentsof sheet “Code notes”

T999999_4.pdf
Page 1 Contentsof sheet “Cover”
Page 2 Contentsof sheet “Certificate”
Page 3 Contentsof sheet “T999999_3”
Page 3 Contentsof sheet “Code notes”

Hopefully this makes sense and thank you in advance for anyassistance.

 

[John_w]

Welcome to MrExcel forums.

Try this macro:

Public Sub Create_Multiple_Sheet_PDFs()

    Dim saveInFolder As String
    Dim tempWb As Workbook
    Dim sheetNames As Variant, sheetName As Variant, i As Long
    
    sheetNames = Array("Cover", "Certificate", "VARIABLE", "Code notes")
    
    saveInFolder = ActiveWorkbook.Path & "\"              'PDFs will be created in this folder
    
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"

    Application.ScreenUpdating = False
    
    With ActiveWorkbook
        For i = 6 To .Worksheets.Count
            Set tempWb = Nothing
            For Each sheetName In sheetNames
                If sheetName = "VARIABLE" Then sheetName = .Worksheets(i).Name
                If tempWb Is Nothing Then
                    .Worksheets(sheetName).Copy
                    Set tempWb = ActiveWorkbook
                Else
                    .Worksheets(sheetName).Copy after:=tempWb.Worksheets(tempWb.Worksheets.Count)
                End If
            Next
            tempWb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & .Worksheets(i).Name & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            tempWb.Close False
        Next
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub