print pdf with tab as file name

[Trevor3007]

hi,

is it possible to print the in sheet1 , sheet3 & sheet2 (in that order) save as their tab names to the following locations:-

sheet1 = c:\data

(print area is A1:I23

sheet3 = c:\data2

(print area is A1:O15

sheet2 = c:\data3

(print area is A1:H22)

and print each as a PDF?

Please can some kind code 'guru' sort for me as I am desperate.

MTIA
Trevor3007

 

[Domenic]

Let's try activating each sheet so that it becomes the active sheet before setting the print area and exporting to PDF...

    For i = LBound(vSheetNames) To UBound(vSheetNames)
        With Worksheets(vSheetNames(i))
[COLOR=#ff0000]            .Activate[/COLOR]
            .PageSetup.PrintArea = vPrintAreas(i)
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=vFolders(i) & "\" & vSheetNames(i) & ".pdf", IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next i

Does this help?

 

[Trevor3007]

Let's try activating each sheet so that it becomes the active sheet before setting the print area and exporting to PDF...

    For i = LBound(vSheetNames) To UBound(vSheetNames)
        With Worksheets(vSheetNames(i))
[COLOR=#ff0000]            .Activate[/COLOR]
            .PageSetup.PrintArea = vPrintAreas(i)
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=vFolders(i) & "\" & vSheetNames(i) & ".pdf", IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next i

Does this help?

hi,

please see link

https://www.amazon.co.uk/clouddrive/share/YbK7uJC7F7Vf5m0CI1qZV197FjK6fCPiJtT9OPO4VlG

still getting error :{
MTIA

 

[Domenic]

For debugging purposes, try the following code, making sure that you use the actual folder names and sheet names, and then post back with the contents of the message that pops up when the error occurs...

Sub ExportSheetsToPDF()

    Dim vFolders As Variant
    Dim vSheetNames As Variant
    Dim vPrintAreas As Variant
    Dim i As Long
    
    On Error GoTo ErrHandler
    
    vFolders = Array("C:\Data", "C:\Data2", "C:\Data3") 'use actual folder names
    vSheetNames = Array("Sheet1", "Sheet2", "Sheet3") 'use actual sheet names
    vPrintAreas = Array("A1:I23", "A1:O15", "A1:H22")
    
    For i = LBound(vSheetNames) To UBound(vSheetNames)
        With Worksheets(vSheetNames(i))
            .PageSetup.PrintArea = vPrintAreas(i)
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=vFolders(i) & "\" & vSheetNames(i) & ".pdf", IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next i
    
ErrHandler:
    MsgBox vFolders(i) & "\" & vSheetNames(i) & ".pdf" & vbCrLf & IIf(Len(Dir(vFolders(i), vbDirectory)) = 0, vFolders(i) & " does not exist", vFolders(i) & " exists"), vbInformation
    
End Sub