VBA to save each page in excel as PDF with Certain File Name

[dabbott]

Is there a VBA code in which i can have an excel sheet with 20 pages, to have each of the 20 pages saved in a certain folder (C:/My Work) as PDF with certain file names? I have 20 pages of locations and i need each location to be saved as a PDF with the city's name as the file name. Like Denver as Denver.pdf right now i have having to manually type in each name and this is a process i repeat many times everytime someone has me change any data. Thanks.

 

[nd1marcello]

Does the city name exist in each of the sheets? Along with a date perhaps? This is possible to do, but I am assuming you want to keep a record, so each save would have to contain some unique name so it doesn't overwrite the other projects.

 

[Jeffrey Lopez]

You could use something like this:

Sub PDF()


For Each ws In ActiveWorkbook.Worksheets


strPDFName = ws.Name
strDir = "C:\Temp"
ChDir strDir
fileSaveName = ws.Name
    
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False


Next ws


End Sub

But like ndmarcello said, it will overwrite anything that was "exported" prior to running the macro. So you may need to change the format, maybe with a Date and the Sheet name?

 

[dabbott]

The city name is on the sheet. Right now i have 20 citys on one tab in excel tab is Sheet1. Sheet1 has 20 pages that i have setup. Page 1 is Denver Page 2 is Dallas, ext. What i am wanting it to do is go in an save Page One in the folder C:\My Work and Denver.pdf. I am planning on it overwriting the previous file as each time i run it i will copy the files to another location.

 

[nd1marcello]

Wrath's code is very good for what you need, but I am confused. So each city is not on its own tab? What do you mean by 'pages' in a tab?

 

[dabbott]

No each city is not is own tab. If it needs to be i can adjust but i have a tab Sheet1 which have polpulation and other data for each city on it. i have format it to where i print that tab it prints on 20 pages one for each city. This is a simple use of this. I am going to try and use the same code to do this on a much large excel workbook that has 20 tabs Sheet1, Sheet2, ect and each sheet is for a different type of information for the cities. here is my current macro that just prints a paper copy for me.

Sub Macro3()
'
' Macro3 Macro
'
'
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveWindow.SelectedSheets.PrintOut From:=4, To:=4, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
End Sub

What i am trying to do is get each page within the sheet to be saved as a certain file name. like from 1 to 1 to always be saved as Denver.pdf 2 to 2 to be Dallas.pdf.

 

[nd1marcello]

In order for Wrath's code to work, you will have to create a separate sheet for each city. Then, each sheet will have to be named exactly how you want the file to be saved as. So naming the sheet with Denver data as "Denver" will save it as Denver.pdf. You will have to fill in the "str.Dir =" as whatever filepath location you want this saved in. It is much more difficult to save each 'print page' area as its own file, and probably out of my area of expertise.

 

[Jeffrey Lopez]

md1marcello is right, mine is set up to print out tabs, not all on one sheet, I misunderstood what you were looking for... as to what you are ACTUALLY looking for... not sure how that can be accomplished, because I am not aware of a way that you can "Save" just 1 page on a tab. You can print out only one page, but you will still have to manually go though and type the names as they printed out.. and you would have to print out to a PDF Printer... so I am not sure how that can be done...

 

[dabbott]

I have edited my worksheet so that each city is on a separate tab. my sub looks like

Sub PDF()

For Each ws In ActiveWorkbook.Worksheets

strPDFName = ws.Name
strDir = "C:\My Work"
ChDir strDir
fileSaveName = ws.Name

ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

Next ws

End Sub

When i run it is looks like it runs but nothing is saved in the folder. I am using Excel 2010 if that matters.

 

[Jeffrey Lopez]

I know this might sound odd, but try saving it to your desktop or something, might be a permissions issue, I created a folder My Work and copied the code just as you have it and was able to save the PDF files. Not sure why yours would not be working.