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]

Try...

Sub ExportSheetsToPDF()

    Dim vFolders As Variant
    Dim vSheetNames As Variant
    Dim vPrintAreas As Variant
    Dim i As Long
    
    vFolders = Array("C:\Data", "C:\Data2", "C:\Data3")
    vSheetNames = Array("Sheet1", "Sheet2", "Sheet3")
    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
    
End Sub

Hope this helps!

 

[Trevor3007]

Try...

Sub ExportSheetsToPDF()

    Dim vFolders As Variant
    Dim vSheetNames As Variant
    Dim vPrintAreas As Variant
    Dim i As Long
    
    vFolders = Array("C:\Data", "C:\Data2", "C:\Data3")
    vSheetNames = Array("Sheet1", "Sheet2", "Sheet3")
    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
    
End Sub

Hope this helps!

hi domenic,

thanks for your help. I ran it but received 2 errors.

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

please see link above for more info.

KR
Trevor3007

 

[Domenic]

Can you post the exact code that you're using, and state which line or lines the errors occur?

 

[Trevor3007]

Can you post the exact code that you're using, and state which line or lines the errors occur?

hi,

I am using the code you sent & the errors dont show which line ...only the error messages are displayed.


Sorry :[

KR
Trevor

 

[Domenic]

You've made no changes whatsoever? As it stands, the code generates no errors. I can't reproduce your errors.

 

[Trevor3007]

You've made no changes whatsoever? As it stands, the code generates no errors. I can't reproduce your errors.

hi,

slight change to the save path... but thats all!!! very odd.

I will try again & get back to you if i mange to sort


thanks anyhoos

 

[Trevor3007]

hi,

i used your code on a complete blank workbook & added 2 x worksheets . Worked a treat.
I can only think that the sheet actual names are causing the issue?

please use link below

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

MTIA

 

[Domenic]

I don't think those sheet names should be a problem. If you named your sheets in your new workbook with those same names do you still get an error?

 

[Trevor3007]

I don't think those sheet names should be a problem. If you named your sheets in your new workbook with those same names do you still get an error?

hi,

thanks for getting back to me.

different error...see link below

https://www.amazon.co.uk/clouddrive/share/5kWwOOHIbD9g2NaCNhj44ibWbvLRaXodjXHoCwW4vDI

fingers crossed its solvable?

MTIA
Trevor3007