VBA Code to save file as PDF on sheet2 and save a copy on specific file.

Hi All
This is my 1st time here and 1st post.

I got 0 knowledge on VBA code. Pertaining to the subject above, i had searched through google almost more than 8 hours. And i got this code,
Sub Create_PDF()
Dim MyFullName As String

Application.ScreenUpdating = False
MyFullName = Left(ThisWorkbook.FullName, InStr(1, ThisWorkbook.FullName, ".") - 1)
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFullName, _
Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Application.ScreenUpdating = True
End Sub

It is working. But what i need is creating PDF through Macro command button on Sheet2, instead of sheet1. And save a copy on specific folder.

Hope that all of the experts here, willing to help me.

Thanks in advance.
Brgds
CY.Chua

AttachmentSize
Screening.xlsm47.4 KB

Please assist me on this.

Please assist me on this. Please

Nick's picture

to assign a macro to a

to assign a macro to a button:

http://www.excelexperts.com/Learn-VBA-Introduction-to-VBA-Editor

to specify the path in the code:

Sub Create_PDF()
Dim MyFullName As String
 
MyFullName = "C:\Temp\FileName.pdf" ' or whatever

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFullName, _
Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

Thanks Nick for your quick

Thanks Nick for your quick responde.
it works well.

But :-
1. The copy of file is not saved on the folder which i locate it.
2. The file name is not follow the range i've selected.
3. It is only create PDF on Sheet1 but not Sheet2. (Even after i alter the code, it works but error to other codes, i dont understand why)

Below is my codes where i search through google.

Private Sub Workbook_Open()
Sheets("Job").Protect _
Password:="q1w2e3r4", _
UserInterfaceOnly:=True
End Sub
Sub SaveScreeningWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
Sheets(Array("Job", "HBL")).Copy
NewFN = "C:\Users\CYCHUA\Documents\JobScreening\" & Range("c2").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextScreening
End Sub

Sub NextScreening()
Range("C2").Value = Range("C2").Value + 1
Range("B5:L15").ClearContents
Range("D19:G34").ClearContents
Range("D35:F57").ClearContents
Range("G40:G41").ClearContents
Range("G46:G47").ClearContents
Range("C4").ClearContents
Range("I1").ClearContents
With Sheets(2)
.Range("A5:E10").ClearContents
.Range("A12:J17").ClearContents
.Range("A19:E24").ClearContents
.Range("F19:J23").ClearContents
.Range("A26:J26").ClearContents
.Range("A28:J28").ClearContents
.Range("A30:J30").ClearContents
.Range("A30:J51").ClearContents
.Range("A54:J54").ClearContents
.Range("A56:G60").ClearContents
.Range("H56:J56").ClearContents
.Range("A62:G62").ClearContents
.Range("A72:J119").ClearContents
End With
End Sub
Sub Create_PDF()
Dim HBL As String
Sheets(Array("HBL")).Copy
NewFN = "C:\Users\CYCHUA\Documents\HBL COPY\" & Range("c2").Value & ".xlsx"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFullName, _
Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

Nick's picture

I think ul need to export

I think ul need to export each worksheet

Or is it possible to create a

Or is it possible to create a macro which will send as PDF attachment on Outlook and it works on sheet2 only?

By the way, i am using 2010 office.

Nick's picture

if it doesn't have to be PDF,

if it doesn't have to be PDF, you can use this:
http://excelexperts.com/VBA-Tips-Email-Workbook

... otherwise, it's a little bit of development to get it all working.. happy to help, but you'll need to request a quote.

http://excelexperts.com/contact