print pdf with tab as file name

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
675
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:
Let's try activating each sheet so that it becomes the active sheet before setting the print area and exporting to PDF...

Code:
    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?
 
Last edited:
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Let's try activating each sheet so that it becomes the active sheet before setting the print area and exporting to PDF...

Code:
    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
 
Upvote 0
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...

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top