Save worksheets in current folder as .pdf

**Birdster** · 11-09-2012, 05:12 AM

I have tried to search here and everywhere it seems but at wits end.

I have made a .xltm template where I enter the info on one sheet and it populates four other sheets in the workbook. I then save this as a .xlsm in the appropiate folder. the other four sheets I have made a concatenate of the name I want to save the worksheet as a .pdf in the current folder the .xlsm is in.

For instance, manually, I select the current tab "Site Summary", file> 'save as', change the file type to .pdf and enter the name (which I already have in 'B19)' and it saves this to the current folder. I can make the macro button on the page, but not the vba to automate to add to the button. I need to make these macros at the template level so when saved to the specific folder, that is where it will save to.

I will not show my ignorance on what my attempts have been, but would like to have someone walk me through one sheet as described above and I can learn to do the other three sheets.

I am using Win7 and Outlook 2010

I do this upteen times a day, would love to free some of my time up....any one willing to help?

Ken

**patel45** · 11-09-2012, 09:20 AM

Sub export_To_Pdf() 
strFilePath = "D:\test\" ' <<<<<<<<< to be changed
 Application.ScreenUpdating = False
On Error Resume Next
For j = 1 To Sheets.count
  strPdfName = Sheets(j).Name
  Sheets(j).Copy
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFilePath & strPdfName, _
  Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
  OpenAfterPublish:=False
       
  ActiveWorkbook.Close (False)
Next
Application.ScreenUpdating = True
End Sub

**Birdster** · 11-11-2012, 05:07 PM

Thank-you for the reply Patel45,

Going to give that a go...

Will be back with admiration or less hair! lol

**Birdster** · 02-11-2013, 04:44 AM

I have finally put together a working code and added it naming one of the files after a specified cell. Thanks again for the head start patel45. Here is the (sloppy) code;

Sub sendpdf01()
'
' sendpdf01 Macro save in current folder with a cell naming the file
'

Dim FileName As String

FileName = ThisWorkbook.Path & "\" & Range("h9").Text & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    
    
End Sub