Print multiple custom views in one pdf with macro VBA code

[IreneFoncillas]

Hi all,

I am quite new with macros in excel, and I was wondering if it is possible to print multiple custom views in one pdf.

For example, I have two custom views named as "CV_1" and "CV_2". How can I print both custom views in a single pdf?

Also, would it be possible to print multiple sheets and custom views in the same pdf? Let's say I want to print, "Sheet 1"; "Sheet 2"; "CV_1"(which is in Sheet 3); "CV_2"(which is in Sheet 3); Sheet 4.

Thank you very much, I hope I have explained myself well

 

[John_w]

Welcome to MrExcel forums.

Try this macro. The PDF is saved in the same folder as the macro workbook.

Public Sub Save_Sheets_and_Custom_Views_As_PDF()

    Dim currentSheet As Worksheet
    Dim tempCVSheets(1 To 2) As Worksheet
    Dim i As Long
    
    With ThisWorkbook
    
        Set currentSheet = .ActiveSheet
        
        'Show CV_1 and copy Sheet3 to a temporary sheet
        
        .CustomViews("CV_1").Show
        .Worksheets("Sheet3").Copy After:=.Worksheets("Sheet3")
        Set tempCVSheets(1) = ActiveSheet
        
        'Show CV_2 and copy Sheet3 to a temporary sheet
        
        .CustomViews("CV_2").Show
        .Worksheets("Sheet3").Copy After:=tempCVSheets(1)
        Set tempCVSheets(2) = ActiveSheet
        
        'Select sheets and save as PDF
        
        .Worksheets(Array("Sheet1", "Sheet2", tempCVSheets(1).Name, tempCVSheets(2).Name, "Sheet4")).Select
       
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\Sheets_CustomViews.pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        
    End With
    
    'Delete temporary custom view sheets
    
    Application.DisplayAlerts = False
    For i = 1 To UBound(tempCVSheets)
        tempCVSheets(i).Delete
    Next
    Application.DisplayAlerts = True
    
    currentSheet.Select
    
End Sub

 

[IreneFoncillas]

Thank you very much John for your quick response. It works perfectly!!! I have tried to modify sheets names and add more customs views and no error at all ?