Looping through worksheets not working

[sjk1193]

I have the code below and seems pretty standard but it only runs for 1 worksheet and doesnt loop through them all. Cant figure out whats worng

Sub formatdata2()

Dim keepcolumn As Variant
Dim fnd As Variant
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
        'Cells(1, 1).Select
    
        LastCol = Cells(1, 1).End(xlToRight).Column
        For j = LastCol To 1 Step -1
            keepcolumn = 0
            If Cells(1, j).Value = "Number" Then keepcolumn = 1
            If Cells(1, j).Value = "Name" Then keepcolumn = 1
            If Cells(1, j).Value = "Base" Then keepcolumn = 1
            If Cells(1, j).Value = "Start Date" Then keepcolumn = 1
            If Cells(1, j).Value = "End Date" Then keepcolumn = 1
            
            If keepcolumn = 0 Then
                Cells(1, j).EntireColumn.Delete
            End If
        Next j
    
        'deletes rows with clasification level = 2
        LastRow = Cells(Rows.Count, 1).End(xlUp).row
        Set fnd = ws.Range("1:1").Find("Which Classify Level", , , xlWhole, , , False, , False)
        
        For i = LastRow To 1 Step -1
            If Cells(i, fnd.Column) = "2" Then
                Cells(i, fnd.Column).EntireRow.Delete
            End If
        Next i
Next ws

End Sub

 

[Joe4]

This line:

For Each ws In ThisWorkbook.Worksheets

will loop through the worksheets, but doesn't actually select or activate them.

So if you have range references in that loop with no sheet reference, they will keep referring to whatever the active sheet is at that time.

You need to either add sheet references in to all of your range references in that loop, or just select/activate that sheet first, i.e.

For Each ws In ThisWorkbook.Worksheets
    ws.Activate