Excel VBA: For Each ws loop doesn't work as expected

[Drakienz]

Hi, new to this forum, hope you guys can help me with my issue altho my expressions are terrible :')

I have attached a picture of how my excel looks like and my code below.
Basically what it's supposed to do is to extract files from different locations and combine them as separate worksheets in one workbook.
Then I want to remove all rows that contains "Outpayment" in column C and all rows that starts with 9 in column G.

Everything works fine until I realise that the code ".Replace "9*", "#N/A", xlWhole, , False" also removes rows that contain "1991" in column G, but this only happens in the Jan22 and Feb22 sheets, not the Mar22 sheet.

Then I tried inputting this code:
"With ws.UsedRange
Dim lngLastRow As Long, lngRow As Long

lngLastRow = Range("G" & Rows.Count).End(xlUp).Row

For lngRow = lngLastRow To 2 Step -1
If Left(Range("G" & lngRow), 1) = "9" Then Rows(lngRow).Delete
Next lngRow
End With"

This removes the rows that starts with 9 in Jan22 and Feb22 sheets but doesn't remove any rows in Mar22 sheet.

Appreciate any help I can get, thank you!

Sub SET05R()

Application.ScreenUpdating = False

Dim wbDest As Workbook
Dim MtName As String
Dim MtPath As String
Dim wbsName As String

Application.DisplayAlerts = False

MtName = Range("G5")
MtPath = Range("G6")
Set wbDest = Workbooks.Add
wbDest.SaveAs Filename:=MtPath & MtName, FileFormat:=xlNormal

Application.DisplayAlerts = False

ThisWorkbook.Activate

Dim wbSc As Workbook
Dim FullPath As String

Const sPath As String = "X:\Carrier Services\IAS\System Reports\SingTel\Month-End\Year"
FullPath = sPath & Range("B1") & "\"
Scol = 9

Do While Scol > 6

ThisWorkbook.Activate
Set wbSc = Workbooks.Open(FullPath & Cells(4, Scol) & "\" & Cells(7, Scol) & Range("J8"))
wbSc.Activate
ActiveSheet.Copy Before:=Workbooks(MtName).Sheets(1)
wbSc.Close False

Scol = Scol - 1

Loop

Workbooks(MtName).Activate
Sheets("Sheet1").Delete
ActiveWorkbook.Save

Dim ws As Worksheet

On Error Resume Next
For Each ws In Sheets
    With ws.UsedRange
      .Replace "Outpayment", "#N/A", xlWhole, , False
      Intersect(.Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    End With
    
    With ws.UsedRange
      .Replace "9*", "#N/A", xlWhole, , False
      Intersect(.Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    End With
    
    ws.Activate
    With Application.ActiveWindow
        Rows("2:2").Select
        .FreezePanes = True
    End With
    With ws
        Range("B:D").EntireColumn.AutoFit
        Range("F:G").EntireColumn.AutoFit
        Range("J:Q").EntireColumn.AutoFit
    End With
Next ws

Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = False

End Sub

 

[Drakienz]

Another thing to add on, I am also getting an error when attempting to reopen the file.