Run-Time error 9: Subscript out of range

[Damian37]

Hello All,
I'm receiving the following run-time error 9 when trying to run the following code. The code appears to work just fine for the first sub, however, I receive the error message when it attempts to run the second sub, and it highlights the Set TableSheet = Worksheets("AllData") line within the code. Not sure why the same code works for one sub, but doesn't work for the second. I'm trying to run this code for 15 regions, but I'm unable to figure out how to add the other regions so the code will work for all 15 regions instead of just 1. Here's the code I currently have:

Sub POCValidation()

Call Region1
Call Region2

End Sub


Sub Region1()

   Dim TableSheet As Worksheet
   Dim NewWB As Workbook
 
   Set TableSheet = Worksheets("AllData")
 
   With TableSheet
 
      .ListObjects("Table6").Range.AutoFilter Field:=3, Criteria1:="Region1"
      .ListObjects("Table6").Range.AutoFilter Field:=5, Criteria1:="Open*"
      .ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:=Array("CSC", "SX", "XM; 2.5", "XM; 2.6", "XM; 3", "XR", "XM; 1", "XM; 2", "Mall Kiosk", "XM", "Kiosk", "3.0", "XR Lite", "Legacy Pre-Paid", "Pop Up", "Pop-Up", "PR", "Sat Loc 3351"), Operator:=xlFilterValues
     
      .Range("Table6[[#Headers],[Store Id]]").Select
      .Range(Selection, Selection.End(xlToRight)).Select
      .Range(Selection, Selection.End(xlDown)).Select
 
   End With
 
   Selection.Copy
   Set NewWB = Workbooks.Add
 
   With NewWB.Worksheets(1)
      .Paste
      Application.CutCopyMode = False
      .Cells.EntireColumn.AutoFit
   End With
 
   NewWB.SaveAs Filename:="C:\Users\Login\Desktop\POC Validation-Request\Non-BP Region1 POC Region " & Format(Date, "mm.dd.yyyy") & ".xlsx", _
                FileFormat:=xlOpenXMLWorkbook, _
                CreateBackup:=False
               

End Sub


Sub Region2()

   Dim TableSheet As Worksheet
   Dim NewWB As Workbook
 
   Set TableSheet = Worksheets("AllData")
 
   With TableSheet
 
      .ListObjects("Table6").Range.AutoFilter Field:=3, Criteria1:="Region2"
      .ListObjects("Table6").Range.AutoFilter Field:=5, Criteria1:="Open*"
      .ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:=Array("CSC", "SX", "XM; 2.5", "XM; 2.6", "XM; 3", "XR", "XM; 1", "XM; 2", "Mall Kiosk", "XM", "Kiosk", "3.0", "XR Lite", "Legacy Pre-Paid", "Pop Up", "Pop-Up", "PR", "Sat Loc 3351"), Operator:=xlFilterValues
     
      .Range("Table6[[#Headers],[Store Id]]").Select
      .Range(Selection, Selection.End(xlToRight)).Select
      .Range(Selection, Selection.End(xlDown)).Select
 
   End With
 
   Selection.Copy
   Set NewWB = Workbooks.Add
 
   With NewWB.Worksheets(1)
      .Paste
      Application.CutCopyMode = False
      .Cells.EntireColumn.AutoFit
   End With
 
   NewWB.SaveAs Filename:="C:\Users\Login\Desktop\POC Validation-Request\Non-BP Region2 POC Region " & Format(Date, "mm.dd.yyyy") & ".xlsx", _
                FileFormat:=xlOpenXMLWorkbook, _
                CreateBackup:=False
               

End Sub

Any and all help is greatly appreciated. Thank you very much.

D.

 

[Max1616]

It's hard to tell without looking at how you are running the subs. Perhaps this error is occurring because the new workbook you create in sub 1 is now the active workbook. So when you run sub 2, the sheet "AllData" doesn't exist.

If this is the case, then you can either close the new workbook at the end of Sub 1:

NewWb.Close False

Or in Sub 2, make sure to activate your original workbook at the top of your code:

Workbooks("Workbook1").Activate

Hope this helps!

 

[Damian37]

It's hard to tell without looking at how you are running the subs. Perhaps this error is occurring because the new workbook you create in sub 1 is now the active workbook. So when you run sub 2, the sheet "AllData" doesn't exist.

If this is the case, then you can either close the new workbook at the end of Sub 1:

NewWb.Close False

Or in Sub 2, make sure to activate your original workbook at the top of your code:

Workbooks("Workbook1").Activate

Hope this helps!

Hi Max,
Thanks so much. I chose to use the close workbook code line you provided as I will be wanting to close each new workbook as it's created. Thank you very much!!!