Run Time error 1004 Loop IF Statement

[Sherilyne]

Hi, a run time error 1004 occurs whenever the macro has finished looping through sheet names which contains "Country Financials". Your help please on what workaround should I do to prevent the error and continue to save the workbook. Thank you in advance for your help!

Sub FormatFiles()
Dim ws  As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False


'Finds the user running the tool
RB1 = Environ("username")


path = "C:\Users" & RB1 & "\Procter and Gamble\FERI-ASIA - 03_Gross Contribution Report\INPUT FILES"


'Working File
Filename = "Asia_HQ_FA_Reporting_Deck" & ".xlsm"


Workbooks.Open Filename:=path & Filename, ReadOnly:=False, notify:=False


Workbooks("Asia_HQ_FA_Reporting_Deck.xlsm").Activate


For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "Country Financials*" Then
ws.Select - (debug occurs here)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="N/A", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="#DIV/0!", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="#REF!", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("L9:AZ73").Select
Selection.Replace What:="MM", Replacement:="000000", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("L9:AZ73").Select
Selection.Replace What:="$", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Next ws


ActiveWorkbook.Save
ActiveWorkbook.Close


MsgBox "Formatting is complete. Please open your KNIME WORKFLOW."


End Sub

 

[footoo]

Do you have any hidden sheets whose names contain "Country Financials" ?

 

[Sherilyne]

Hi! Yes. I currently have sheets with "Country Financials name on it. Should I unhide first those sheets then execute the loop? Thank you so much for your response!

 

[footoo]

You just need to check that the ws is visible :

If ws.Visible And ws.Name Like "Country Financials*" Then

Here's a tidied-up version of your code :

Sub FormatFiles()
Dim ws As Worksheet
Application.ScreenUpdating = False
'Finds the user running the tool
RB1 = Environ("username")
Path = "C:\Users" & RB1 & "\Procter and Gamble\FERI-ASIA - 03_Gross Contribution Report\INPUT FILES"
'Working File
Filename = "Asia_HQ_FA_Reporting_Deck" & ".xlsm"
Workbooks.Open Filename:=Path & Filename, ReadOnly:=False, notify:=False
Workbooks("Asia_HQ_FA_Reporting_Deck.xlsm").Activate
For Each ws In ActiveWorkbook.Worksheets
    With ws
       If .Visible And .Name Like "Country Financials*" Then
        .Cells.Value = .Cells.Value
        .Cells.Replace What:="N/A", Replacement:="0", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        .Cells.Replace What:="#DIV/0!", Replacement:="0"
        .Cells.Replace What:="#REF!", Replacement:="0"
        .Range("L9:AZ73").Replace What:="MM", Replacement:="000000"
        .Range("L9:AZ73").Replace What:="$", Replacement:=""
        End If
    End With
Next ws
ActiveWorkbook.Close saveChanges:=True
MsgBox "Formatting is complete. Please open your KNIME WORKFLOW."
Application.ScreenUpdating = True
End Sub