Macro to print specific sheets from multiple spreadsheets

[dshafique]

Hi everyone, I have folders for each of my students containing a spreadsheet with their performance. every week i have to open up each individual file and print out 2 specific sheets from each file, week x and week x progress report. it usually takes me all morning, but I was wondering if there was a way for me to print out all the sheets I need from every file all at once instead of going into each individual file. (there are at least 30+ students). any and all help would be greatly appreciated!

 

[Hendy2322]

Here is a solution I put together. First I'm assuming the 2 specific sheets you want to print in each workbook are the named exactly the same (week x and week y). After pasting in the code you need to udpate the Const sSHEET1_NAME and Const sSHEET2_NAME from my names ("Sheet1", "Sheet2") to (week x and week y). After that just run the code, it will bring up a dialogue box where you can select the folder containing all the workbooks. Instead of automatically printing, for each sheet the code will open up the print dialogue box, from there you can choose to preview print, choose the printer, etc. I did this so you can be sure that it's correctly printing out each worksheet the way you want. If the first try works smoothly then you can comment out the two "Application.Dialogs(xlDialogPrint).Show" lines and uncomment "wks1.PrintOut" and "wks2.PrintOut" and the code will automatically print everything without prompting the print dialogue box. Let me know if this works for you.

Open up a new excel workbook, paste the below code into a module, and run it.

Option Explicit


Const sSHEET1_NAME As String = "Sheet1"
Const sSHEET2_NAME As String = "Sheet2"


Sub PrintWorksheets()
    
    Dim wb As Workbook
    Dim wks1 As Worksheet
    Dim wks2 As Worksheet
    Dim sPath As String
    Dim sExtension As String
    Dim sFile As String
    Dim SelectFolder As FileDialog
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    'Retrieve Target Folder Path From User
    Set SelectFolder = Application.FileDialog(msoFileDialogFolderPicker)
    
    With SelectFolder
        .Title = "Select A Target Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        sPath = .SelectedItems(1) & "\"
    End With


'In Case of Cancel
NextCode:
    sPath = sPath
    If sPath = "" Then GoTo CleanUp
    
    'Target File Extension (must include wildcard "*")
    sExtension = "*.xls*"
    
    'Target Path with Ending Extention
    sFile = Dir(sPath & sExtension)
    
    'Loop through each Excel file in folder
    Do While sFile <> ""
        'Set variable equal to opened workbook
        Set wb = Workbooks.Open(Filename:=sPath & sFile)
      
        'Ensure Workbook has opened before moving on to next line of code
        DoEvents
        
        'Print Sheets
        Set wks1 = wb.Sheets(sSHEET1_NAME)
        Set wks2 = wb.Sheets(sSHEET2_NAME)
        wks1.Activate
        
        'Comment out Application.Dialogs(xlDialogPrint).Show and uncomment wks1.PrintOut if you don't want to preview prints and your default printer is correct.
        Application.Dialogs(xlDialogPrint).Show
        'wks1.PrintOut
        
        wks2.Activate
        'Comment out Application.Dialogs(xlDialogPrint).Show and uncomment wks1.PrintOut if you don't want to preview prints and your default printer is correct.
        Application.Dialogs(xlDialogPrint).Show
        'wks2.PrintOut
        
        wb.Close SaveChanges:=False
        
        'Ensure Workbook has closed before moving on to next line of code
        DoEvents
        
        'Get next file name
        sFile = Dir
    Loop
    
CleanUp:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[dshafique]

thank you so much, i tried it out, but unfortuantely it didnt do anything after asking me for my folder. my folder structure is such I have a folder containing multiple folders in it, one for each student. each folder only has 1 excel file in it. is there a way for this to look through all folders within a directory? thanks