title: If workbook contains 6 tabs then run current Sub, if workbook contains 9 tabs then run a different Sub
body: <p>I have a workbook that houses the macro which loops through a directory of 800+ workbooks while taking particular cells and copying them to this workbook.  It has worked fine for 2 years until 50 of the 800+ workbooks were changed from 6 tabs to 9 tabs.  </p>
  
  <p>I have <code>For X = 6 To Wb2.Sheets.Count</code> which is throwing the debug error because the new workbooks have 9 tabs.</p>
  
  <p>I have 2 separate codes that need to run to pull those cells depending if the workbook has 6 or 9 tabs and need help.</p>
  
  <p>Here is my working full Sub, any suggestions on how to do this would be a major help.</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
  Dim Wb1 As Workbook, Wb2 As Workbook
      Set Wb1 = ThisWorkbook
  
  Dim WS As Worksheet
  Dim L As Long, X As Long
      spath = "c:\attachments\"
      sfile = Dir(spath &amp; "*.xls*")
  
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
  
      Set WS = Sheets.Add(before:=Sheets(1))
  
      L = 1
          Do While sfile &lt;&gt; ""
              Set Wb2 = Workbooks.Open(spath &amp; sfile)
  
              For X = 6 To Wb2.Sheets.Count
  
  Wb1.Sheets(1).Cells(L, "B").Value = Wb2.Sheets("Daily_Revenue_Management").Range("C33").Value
  
  Wb1.Sheets(1).Cells(L, "C").Value = Wb2.Sheets("Daily_Revenue_Management").Range("D3").Value
  
  Wb1.Sheets(1).Cells(L, "D").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("D4").Value
  Wb1.Sheets(1).Cells(L, "E").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("D5").Value
  Wb1.Sheets(1).Cells(L, "F").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("D6").Value
  Wb1.Sheets(1).Cells(L, "G").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("D7").Value
  Wb1.Sheets(1).Cells(L, "H").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("D8").Value
  Wb1.Sheets(1).Cells(L, "I").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("E4").Value
  Wb1.Sheets(1).Cells(L, "J").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("E5").Value
  Wb1.Sheets(1).Cells(L, "K").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("E6").Value
  Wb1.Sheets(1).Cells(L, "L").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("E7").Value
  Wb1.Sheets(1).Cells(L, "M").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("E8").Value
  
  Wb1.Sheets(1).Cells(L, "N").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("G4").Value
  Wb1.Sheets(1).Cells(L, "O").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("G5").Value
  Wb1.Sheets(1).Cells(L, "P").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("G6").Value
  Wb1.Sheets(1).Cells(L, "Q").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("G7").Value
  Wb1.Sheets(1).Cells(L, "R").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("G8").Value
  Wb1.Sheets(1).Cells(L, "S").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("H4").Value
  Wb1.Sheets(1).Cells(L, "T").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("H5").Value
  Wb1.Sheets(1).Cells(L, "U").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("H6").Value
  Wb1.Sheets(1).Cells(L, "V").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("H7").Value
  Wb1.Sheets(1).Cells(L, "W").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("H8").Value
  Wb1.Sheets(1).Cells(L, "X").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("I4").Value
  Wb1.Sheets(1).Cells(L, "Y").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("I5").Value
  Wb1.Sheets(1).Cells(L, "Z").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("I6").Value
  Wb1.Sheets(1).Cells(L, "AA").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("I7").Value
  Wb1.Sheets(1).Cells(L, "AB").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("I8").Value
  
  Wb1.Sheets(1).Cells(L, "AC").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("L4").Value
  Wb1.Sheets(1).Cells(L, "AD").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("L5").Value
  Wb1.Sheets(1).Cells(L, "AE").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("L6").Value
  Wb1.Sheets(1).Cells(L, "AF").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("L7").Value
  Wb1.Sheets(1).Cells(L, "AG").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("L8").Value
  Wb1.Sheets(1).Cells(L, "AH").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("M4").Value
  Wb1.Sheets(1).Cells(L, "AI").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("M5").Value
  Wb1.Sheets(1).Cells(L, "AJ").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("M6").Value
  Wb1.Sheets(1).Cells(L, "AK").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("M7").Value
  Wb1.Sheets(1).Cells(L, "AL").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("M8").Value
  Wb1.Sheets(1).Cells(L, "AM").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("N4").Value
  Wb1.Sheets(1).Cells(L, "AN").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("N5").Value
  Wb1.Sheets(1).Cells(L, "AO").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("N6").Value
  Wb1.Sheets(1).Cells(L, "AP").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("N7").Value
  Wb1.Sheets(1).Cells(L, "AQ").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("N8").Value
  
  Wb1.Sheets(1).Cells(L, "AR").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("Q4").Value
  Wb1.Sheets(1).Cells(L, "AS").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("Q5").Value
  Wb1.Sheets(1).Cells(L, "AT").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("Q6").Value
  Wb1.Sheets(1).Cells(L, "AU").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("Q7").Value
  Wb1.Sheets(1).Cells(L, "AV").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("Q8").Value
  Wb1.Sheets(1).Cells(L, "AW").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("R4").Value
  Wb1.Sheets(1).Cells(L, "AX").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("R5").Value
  Wb1.Sheets(1).Cells(L, "AY").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("R6").Value
  Wb1.Sheets(1).Cells(L, "AZ").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("R7").Value
  Wb1.Sheets(1).Cells(L, "BA").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("R8").Value
  Wb1.Sheets(1).Cells(L, "BB").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("S4").Value
  Wb1.Sheets(1).Cells(L, "BC").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("S5").Value
  Wb1.Sheets(1).Cells(L, "BD").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("S6").Value
  Wb1.Sheets(1).Cells(L, "BE").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("S7").Value
  Wb1.Sheets(1).Cells(L, "BF").Value = Wb2.Sheets("Rolling 4month Snapshot").Range("S8").Value
  
      L = L + 1
          Next
              Wb2.Close False
              sfile = Dir()
          Loop
              ActiveWorkbook.Save
              Application.ScreenUpdating = True
              Application.Calculation = xlCalculationAutomatic
      If L &gt; 0 Then
  End If
  
  
  Sheets("Sheet1").Select
  Range("B1:B1000").Select
  Range(Selection, Selection.End(xlToRight)).Select
  Selection.Copy
  Sheets("data").Visible = True
  Sheets("data").Select
  Range("B1").Select
  ActiveSheet.Paste
  Sheets("data").Select
  ActiveWindow.SelectedSheets.Visible = False
  Sheets("Sheet1").Select
  Application.CutCopyMode = False
  ActiveWindow.SelectedSheets.Delete
  
  Sheets("Dashboard").Select
  Range("A5").Select
  ActiveCell.FormulaR1C1 = "Updated: " &amp; Format(Date, "MM/DD/YYYY")
  Range("A6").Select
  
  UserForm1.Hide
  
  End Sub
  </code></pre>