I was recently asked by a client to generate some VBA code to select sheets and print them in PDF format. I have created a couple of posts on how to generate PDF reports in Excel with VBA but this was slightly different. I had to work with sheet selection. So the tricky bit was not the PDF part because I already had that. The slightly goofy thing about this code is when the sheets are already selected you need to select each sheet in the selection stack to get the print part of the VBA to work. The following is the procedure which gets the job done.
Option Explicit
Sub PDFCreate() 'Excel VBA to batch some files to PDF (very useful).
Dim sh As Worksheet
For Each sh In ActiveWindow.SelectedSheets
sh.Select 'Now do something practical.
sh.ExportAsFixedFormat xlTypePDF, Range("Drive") & sh.[B4] & ".pdf"
Next sh
End Sub
The code is elegant and runs really well. Remember it works on the sheets which are selected. Remember to hold the Ctrl key to select multiple sheets at once and then run this puppy over the top of your model. Be aware, the Range “Drive” above has a named range in that range is the path.
“C:\Users\marcus\Downloads\”
Don’t forget to include the backslash at the end, don’t forget to include the back slash \ DID I SAY THAT twice? Well now you know.
My other articles on PDF creation:
Alternatively if you know the sheets you wish to be excluded from your selections this was one of the iterations on the journey in achieving the above code. It will select all the sheets which are not worksheet code names 1-4. If you have followed my site closely you will know I advocate for the sole use of the worksheet code name when writing VBA code. This is not always possible and I know that makes a nonsense of the prior sentence but the worksheet code name is the bad boy that makes you look like a coding rock star. Use it relentlessly. Use it and sing it loudly. It is the bird that will make your code fly in the face of users who change the sheet name on you. It makes so much sense and will save you time and again. I am ranting now.
Go forth make solid code and always ask this poignant question - ‘How do I do what I am currently doing… better?’ It is the only way to improve and it is a motto for a well lived life.
Sub DelSh()
Dim ws As Worksheet
For Each ws In Sheets
Select Case ws.CodeName
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", ActiveSheet.CodeName
Case Else
ws.Select
End Select
Next ws
End Sub
The above makes the bold assumption that the workbook you are using has more than 4 sheets and of course their worksheet code names are 1-4. It will probably work if your sheet does not though.
All the very best -
Smallman
