Print worksheet from multiple workbooks

[Mike Daniels]

Need to print a worksheet (the tab is named "MTG -- NEEDS") from 26 monthly facility workbooks. January's Workbooks were named with their monthly extension of AURORA - 0111, BRIDGEVIEW - 0111, through WOODSTOCK - 0111. For February it was AURORA - 0211, BRIDGEVIEW - 0211, and WOODSTOCK -0211. And so on. Just want to select that particular worksheet from each facility file and print, or send to a single file for printing. Unfortunately I don't understand VBA very well, to know if this is possible or how to do it.

 

[pboltonchina]

Are all the files in the same folder?

 

[Mike Daniels]

Yes, they can be. I email them to my managment and he saves them in a single monthly folder.

 

[pboltonchina]

Try this on a copy of your data as it is untested

Public Sub All_Files_in_Folder()

    Dim folder As String
    Dim filename As String
    Application.ScreenUpdating = False
    folder = "C:\SAP Imports\BOMs\" 'change path to suit
    If Right(folder, 1) <> "\" Then folder = folder & "\"
       
    filename = Dir(folder & "*.ped", vbNormal)
    Do Until filename = ""
        Worksheet("MTG -- NEEDS").Activate
        ActiveSheet.PrintOut Copies:=1, Collate:=True
        Workbook.Close SaveChanges:=False
        filename = Dir()  ' Get next matching file
    Loop
      Application.ScreenUpdating = True
End Sub

 

[Mike Daniels]

Thank you.