FashionGal
New Member
- Joined
- Mar 24, 2019
- Messages
- 5
Hello,
I want to create a macro that allows me to physically print an excel spreadsheet and then automatically saves the spreadsheet as a PDF file in a particular location at the same time. The file name is unique to the information that is located in 2 various cells - T3 and L32. I will need to change some information on the sheet and will need to print/save the file multiple times and when it saves, I do not want the saved file to be overwritten with any new changes. Is there a way if there is already a file with that name to add a 01, 02, 03, etc. to the end of the file name automatically? Below is the macro that I have been using to print the excel sheet to paper and the macro that I have been using to save the excel sheet as a PDF. Any help would be greatly appreciated as I am new to creating macros.
Printing the File Macro
Sub PrintPaper()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub
Saving the File as a PDF Macro
Sub PTPDF2()
Dim pdfName As String, FullName As String, Path As String
pdfName = Sheets("Award Sheet").Range("T3").Value
Path = "I:\Student Awarding\2019-20\APPEALS"
FullName = Path & pdfName & ".pdf"
Application.ScreenUpdating = False
ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName, OpenAfterPublish:=False
Application.ScreenUpdating = True
End Sub
I want to create a macro that allows me to physically print an excel spreadsheet and then automatically saves the spreadsheet as a PDF file in a particular location at the same time. The file name is unique to the information that is located in 2 various cells - T3 and L32. I will need to change some information on the sheet and will need to print/save the file multiple times and when it saves, I do not want the saved file to be overwritten with any new changes. Is there a way if there is already a file with that name to add a 01, 02, 03, etc. to the end of the file name automatically? Below is the macro that I have been using to print the excel sheet to paper and the macro that I have been using to save the excel sheet as a PDF. Any help would be greatly appreciated as I am new to creating macros.
Printing the File Macro
Sub PrintPaper()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub
Saving the File as a PDF Macro
Sub PTPDF2()
Dim pdfName As String, FullName As String, Path As String
pdfName = Sheets("Award Sheet").Range("T3").Value
Path = "I:\Student Awarding\2019-20\APPEALS"
FullName = Path & pdfName & ".pdf"
Application.ScreenUpdating = False
ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName, OpenAfterPublish:=False
Application.ScreenUpdating = True
End Sub