Help with VBA to process 10k + files each with mulitple sheets

**darin.m** · 04-22-2014, 03:04 PM

Good Afternoon,

To begin with, I want to thank all of you as you have already been a huge help in how far I have gotten. I would not have gotten here with out you.

Now my problem if you would please allow me to beg for help on.

I have 10,000+ excel workbooks, and each have multiple sheets (there a total of 12 sheets, but not all sheets are in all workbooks).

I need to set all sheets to a known print area as well as set each sheet to portrait format and set the printing default to print all columns on one page with the script ending in saving the file/workbook.

Ideally I would then like the script to move on to the next file/workbook in the folder.

The code below is what I have so far, but it is not working, and I can not figure out why or where. However it complies with no errors.

So again, if you will please allow me, I am begging for help. This is for work, and I really don't want to have to go through all the sheets one by one.

I really hope I have explained what I need and am wanting.

Thank you in advance for anything insight you can offer.

*************************************************

Function WorksheetExists(SH As String)
Dim Sht As Worksheet
WorksheetExists = False
For Each Sht In ActiveWorkbook.Worksheets
  If Sht.Name = SH Then
    WorksheetExists = True
    Exit For
  End If
 Next Sht
End Function


Sub print_area()
'
' print_area Macro
'
' Keyboard Shortcut: Ctrl+s
'

      
       
    If WorksheetExists("Tally Board") Then
        Sheets("Tally Board").Select
        Sheets("Tally Board").Activate
        Range("A1:J20").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$J$20"
       With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
    If WorksheetExists("Inmate Location") Then
        Sheets("Inmate Location").Select
        Sheets("Inmate Location").Activate
        Range("A1:E50").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$E$50"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
    If WorksheetExists("Inmate Location A-D") Then
        Sheets("Inmate Location A-D").Select
        Sheets("Inmate Location A-D").Activate
        Range("A1:E50").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$E$50"
       With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
        
    If WorksheetExists("Inmate Location E-G") Then
        Sheets("Inmate Location E-G").Select
        Sheets("Inmate Location E-G").Activate
        Range("A1:E50").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$E$50"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
        
    If WorksheetExists("Activity") Then
        Sheets("Activity").Select
        Sheets("Activity").Activate
        Range("A1:L155").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$L$155"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
        
    If WorksheetExists("Security Check") Then
        Sheets("Security Check").Select
        Sheets("Security Check").Activate
        Range("A1:I110").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$I$110"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
    
    If WorksheetExists("Security Check HU3") Then
        Sheets("Security Check HU3").Select
        Sheets("Security Check HU3").Activate
        Range("A1:I110").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$I$110"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
    
    If WorksheetExists("Security Check HU4") Then
        Sheets("Security Check HU4").Select
        Sheets("Security Check HU4").Activate
        Range("A1:I110").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$I$110"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
    
    If WorksheetExists("Security Check HU16") Then
        Sheets("Security Check HU16").Select
        Sheets("Security Check HU16").Activate
        Range("A1:I110").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$I$110"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
    
    If WorksheetExists("Security Check HU17") Then
        Sheets("Security Check HU17").Select
        Sheets("Security Check HU17").Activate
        Range("A1:I110").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$I$110"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
    
    If WorksheetExists("DVD-CD Check Out") Then
        Sheets("DVD-CD Check Out").Select
        Sheets("DVD-CD Check Out").Activate
        Range("A1:F110").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$F$110"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
         End With
    End If
        
    If WorksheetExists("HU Check Off") Then
        Sheets("HU Check Off").Select
        Sheets("HU Check Off").Activate
        Range("A1:S15").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$S$15"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
        
    If WorksheetExists("SCBA") Then
        Sheets("SCBA").Select
        Sheets("SCBA").Activate
        Range("A1:G20").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$G$20"
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    
                
    If WorksheetExists("Recreation") Then
        Sheets("Recreation").Select
        Sheets("Recreation").Activate
        Range("A1:G260").Select
           ActiveSheet.PageSetup.PrintArea = "$A$1:$G$260"
        Range("F274").Select
        With ActiveSheet.PageSetup
           .PaperSize = xlPaperLetter
           .Orientation = xlPortrait
           .FitToPagesWide = 1
           .FitToPagesTall = False
        End With
    End If
    

End Sub

**********************************************