Select Method of worksheet class failed Error - VBA

[jeffboy29]

Hi,

I'm encountering Select Method of worksheet class failed Error while running below vba code:

It failed on Sheets(strSheetName).Select

Sub mc_Breakout()
'
' Breakout Macro
'

Dim intI As Integer
Dim strSheetName As String
Dim strFileName As String
Dim RngBal As Range
   


On Error GoTo ErrorHandler

   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   
'    For intI = 3 To Worksheets.Count
    For intI = 3 To 11
    
    strSheetName = Worksheets(intI).Name
    If strSheetName <> "Ls_XLB_WorkbookFile" And strSheetName <> "Ls_AgXLB_WorkbookFile" Then
    
    Sheets(strSheetName).Select
' Copy-Paste Values Headings
    Range("B3:B7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
' Copy-Paste Values Extract Balances
   Range("A1").Select
   
  Set RngBal = Cells.Find(What:="=LOOKUP", LookIn:=xlFormulas, SearchDirection:=xlNext, SearchOrder:=xlByRows)
  
    If RngBal Is Nothing Then
    GoTo NxtProc
    Else
    
    Cells.Find(What:="=LOOKUP", LookIn:=xlFormulas, SearchDirection:=xlNext, SearchOrder:=xlByRows).Select
  
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    GoTo NxtProc
    
    End If
     
NxtProc:
 
    Range("B1").Select
          
'Protect Sheet
        ActiveSheet.Protect ("fr2007")
        Content = ActiveSheet.Name
        
        Ofcname = Sheets("Parameter").Range("B6").Value
        DateString = Format(Now, "yyyy-mm-dd")
        Sheets(strSheetName).Copy
  
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Ofcname & "_" & Content & "_" & DateString & ".xls", FileFormat:=xlNormal, _
                    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
                    CreateBackup:=False
       
        ActiveWorkbook.Close
    
    End If
     
     Next intI
     
     MsgBox "Report Completed.  Please check folder for generated files"
     
     Application.ScreenUpdating = True
    
    ActiveWorkbook.Saved = True
    Application.DisplayAlerts = True
    Application.Workbooks.Close
    Application.Quit
    
Exit Sub

ErrorHandler:
   MsgBox Error(Err)
End Sub

I need to select sheets T0 – T9 & PN.

(Please see attached file)

The following sheets are not visible & don’t need to select:

Sheet 12 (tmpscrapsheet)
Sheet 13 (Ls_XLB_WorkbookFile)
Sheet 14 (Ls_AgXLB_WorkbookFile)

Hope you can help me.


Thanks!

Jeff