As pointed out in comments, you're not using your With ws statement. I saw this issue arrise in another one of your questions earlier in the month too so I thought I'd give an explanation for how to use the statement correctly.
Consider this code:
Sub WithStatementExample()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
Range("A1").Value = "This cell is A1 and this sheet name is " & ws.Name
ActiveSheet.Range("A2").Value = "This cell is A2 and this sheet name is " & ws.Name
End With
End Sub
Now although our statements are encapsulated within the With statement, the output to cells A1 and A2 will actually both be on whatever sheet is active at the time the code is executed.
As example, consider this new workbook with 3 sheets, all with default names:
If we execute the code based on the workbook in that screenshot, the results will go where we are assuming they will - Sheet1 cells A1 and A2. But if we run the code whilst Sheet3 is active, the code outputs the values to Sheet3 cells A1 and A2.
This is because within our With statement, we have not used our ws worksheet as the object qualifier for the Range property.
The range property can apply to both the Application and Worksheet objects and per Application.Range documentation:
When used without an object qualifier, this property is a shortcut for ActiveSheet.Range (it returns a range from the active sheet; if the active sheet isn't a worksheet, the property fails).
To get the statements within the With block using the object, type a . in front of your statements:
Sub WithStatementExample()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
.Range("A1").Value = "This cell is A1 and this sheet name is " & ws.Name
.Range("A2").Value = "This cell is A2 and this sheet name is " & ws.Name
End With
End Sub
Now the output will only go to the sheet assigned to the ws variable - in this case, Sheet1.
If you need some statements to be applied to other sheets but executed within your With block, you are better off using an explicit reference to the workbook rather than ActiveSheet - This will help explain more - (How to avoid using select in vba)
So if the ListObjects in your code were actually on Sheets("TOC") not whichever ws the loop is referring to, I'd change ActiveSheet.ListObjects... and ActiveSheet.ListObjects("Table1")... to:
wb.Sheets("TOC").ListObjects...
wb.Sheets("TOC").ListObjects("Table1")...
With your For Each...Next loop, your variable ws in this case represents an object - specifically a worksheet. The variable ws is assigned the next object at each iteration, so you don't need to set the worksheet object to the variable independently to the loop.
In my example above I had 3 sheets. The two below code blocks will achieve the same outcome, one via a For Each...Next loop and the other without.
For Each...Next approach:
Sub ForEachExample()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
Next ws
End Sub
No loop approach:
Sub NoLoopExample()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1) 'The same as Sheets(1) or Sheets("Sheet1")
Debug.Print ws.Name
Set ws = ThisWorkbook.Sheets(2) 'The same as Worksheets(2) or Sheets("Sheet2")
Debug.Print ws.Name
Set ws = ThisWorkbook.Sheets("Sheet3") 'The same as Worksheets(3) or Sheets(3)
Debug.Print ws.Name
End Sub
Both of them will output the following to the immediate window in the VBE:
Sheet1
Sheet2
Sheet3
In your updated code, you are setting ws to ThisWorkbook.Sheets("Sheet2").
- You will need to have a worksheet named
"Sheet2" in your workbook or you will get a Runtime Error 9: subscript out of range error.
- The next line in your code is the
For Each...Next statement which will set ws to the first sheet in the Worksheets collection, regardless of the previous line of code.
What I'm pointing out is that line of code is redundant in your current procedure and depending on the names of your worksheets, will probably be causing a runtime error.
See the For Each...Next statement documentation.
With your Select Case statement, your first line Select Case ws.Names should throw a Compile Error: Type mismatch. ws.Names returns the collection of worksheet specific names based on the object qualifyer (to it's left) - in this case ws. Because we are looking for the sheets name specifically, it should be ws.Name which returns the name of the object qualifyer.
So to take that into account it could look something like:
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "TOC"
'Do nothing
Case Else
'Do your actions here
End Select
Next ws
As a side note, you could also achieve the same with an If...Else statement like so:
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "TOC" Then 'Note by using = "TOC" must match exactly, including letter casing.
'Do nothing
Else
'Do your actions here
End If
Next ws
