Hi Excellers in today’s Macro Monday let’s look at creating a Macro to print all of the Excel workbooks in a folder. If you have a bunch of workbooks in an Excel folder and you need print all of them together, then this is the time saving piece of VBA code you need rather than opening a multitude of files, and then individually printing them.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.
How To Excel At Excel – Macro Mondays Blog Posts.
[wpdm_package id=’10338′]
What Does The Macro Do?
This Macro uses the Dir Function which returns a string that will represent the name of the file that you want to print. The Dir Function will loop through all of the .xlsx files in a given folder, open them, then print them, then re close the file.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Right Click on personal.xbl in the Project Window. I have stored this macro in my Personal Macro Workbook, so it is available to use whenever I start Excel and can apply it to any Excel workbook I choose to.
Step 2. We need to declare a variable. This ensures that Excel creates a memory container for the files to print. We declare the MyFilesToPrint which will represent the name of file we want to print.
Dim MyFilesToPrint As String
Step 3. Excel now uses to Dir Function to specify both the Folder and the File Type to find. This code will be looking for .xlsx file extensions, any other type will need to be specified (such as xls).
Step 4. This next step will open each of the files and print each one of the workbooks (Sheet1), the close them again.
Step 5. Excel then loops back to the next file until all files have been printed.
Want The Code?
Sub PrintAllWorkbooks()
Dim MyFilesToPrint As String
MyFilesToPrint = Dir(“C:\Users\*.xlsx”)
Do While MyFilesToPrint <> “”
Workbooks.Open “C:\Users\” & MyFilesToPrint
ActiveWorkbook.Sheets(“Sheet1”).PrintOut Copies:=1
ActiveWorkbook.Close SaveChanges:=False
MyFilesToPrint = Dir
Loop
End Sub
