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
| Attachment | Size |
|---|---|
| Screening.xlsm | 47.4 KB |
Please assist me on this.
Please assist me on this. Please
to assign a macro to a
http://www.excelexperts.com/Learn-VBA-Introduction-to-VBA-Editor
to specify the path in the code:
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
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.
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