Updated: Feb 3, 2021
Please see the following code for printing or saving your sheets through VBA.
An analyst might for example use printing functions for looping through vendors and creating a bunch of snail mails, where the suppliers information is iterated to update addresses on to whom the envelope should be addressed to, company, address, country etc.
However, whilst this might go quick to set up in VBA considering it is only 1 simple loop with a few fields that are being amended for each supplier. The task of folding and putting a few hundred documents into the envelops after being printed can not be automated...
Code for printing
'Activate the sheet that should be printed and run this.
ActiveSheet.PrintOut
'If you need to specify the print pages.
Worksheets. ("sheet1").PrintOut From:=2, To:=3
'If you need to print copies as well (probably not for this example though, but could be good to know. Maybe Boardmeeting prep.)
Worksheets. ("sheet1").PrintOut From:=2, To:=3, Copies:=77
Code for saving excel, 1 sheet or multiple
A worksheet can either be referenced on its code name that can be amended in the property window.
Alternatively it can be referenced by its tabname. I am mostly using the codename, as that is better practice. Shorter, and reduces the risk of someone amending the tabname and ruining the connection in your code.
Example:
MySheet = the code name for the sheet in the properties window.
MySheet = sheets ("Sheet1")
'*************************Saves 1 page in either .xlsx or pdf****************************
'Saves as .pdf format.
MySheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strPath, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
'Saves as .xlsx format
MySheet.Copy
With ActiveWorkbook
.SaveAs FileName:=strPath & ".xlsx", FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
End With
'************************ Saves multiple pages in either .xlsx or pdf***********************
'Saves as .pdf
Sheets(Array(MySheet.Name, MySheet2.Name)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strPath, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
'Saves as .xlsx format
Sheets(Array(MySheet.Name, MySheet2.Name)).Copy
With ActiveWorkbook
.SaveAs FileName:=strPath & ".xlsx", FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
End With
'*****************************Overwrite an existing file**********************************
'Dir syntax checks the directory if the file exists already.
'StrPath = is my pathway for the file.
'In this example I am checking for a pdf file, however it could be any sort of file.
If Dir(strPath & ".pdf") <> "" Then
'If file exists, put out a yes/no message box that will allow the user to decide whether or not to overwrite the file.
YesNo = MsgBox("Overwrite" & " " & FileName & "?", vbYesNo)
'If yes, then overwrite.
If YesNo = vbYes Then
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strPath, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
'If file doesn't exists, then save as normal.
Else
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strPath, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If