You are working in your highly automated super excel spread sheet that you have created. After all these months of additions and formulas, cell updates and tests you are there! You finally mad your spread sheet fully automated that produces alone cells, numbers, prices and data that would take days to calculate them manually.But…
Something is missing…
Every single time you want to export your beloved data and send them with an email in a PDF format you have to do the same repetitive work. CTRL+ left click to all the sheets you created, then file, export to pdf…
Every single time…
Now with a simple but handy VBA code the magic will happen and your workbook will at the point where you want it to be.
First things first. Do you know how to add VBA code to excel?
If yes then good for you. If no, then see this video.
If you don’t have the “Developer” Tab in Excel then follow this link
It is just a 2 step process. Nothing special and difficult.
So now that you know where to paste the code you actually need the code. And here it is!
Option Explicit
Sub SaveSheetsasPDF()
Dim strFileName As String
strFileName = Replace(ThisWorkbook.Name, ".xlsm", "")
ThisWorkbook.Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & strFileName & " - Export", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Sheets("Sheet 1").Select
End Sub
Simple as that.
Now you have to tweak this a little bit.
First of all.
Where it says:
Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4")).Select
Just replace the Sheet 1 etc with the sheet names you have given and you want to print. It they are less than 4 obviously delete what you don’t need or if they are more just add another tab name in brackets.
Secondly, 5 lines before the end line of the code there is the code that says:
ThisWorkbook.Path & "\" & strFileName & " - Export", _
If you want to add a description at the end of the file name just change the -Export with whatever you want. If not, then delete the code after the the strFileName ( & ” – Export”) and let the comma and the underscore.
Thirdly, on the line before the last one is the code:
Sheets("Sheet 1").Select
This is because without this code the sheets you have selected to be exported will remain selected and this may cause problem. So, I thought it was more handy to just select Sheet 1 at the end of the process. If you want a specific sheet to be selected then between the brackets just change the name with the name of the sheet you would like.
Disclaimer: You should first have to set the printing areas before exporting but I guess you are working in this way from default. I personally prefer to work as 1 page wide and as many pages as it takes for height (Layout tab).
That’s all.
Bonus tip – Add a button to run the above code