Please help removing my colons

[WalkAlot]

Hi all,

I've got a large Excel document with about 16 worksheets. Alot of the cells have a colon at the end of the strng, but not all. I need to remove all of the colons sp that my Index Match formulas will work. I've recorded quide a detailed Macro and added the code in to remove the colons as I found a similar post about removing semicolons earlier but this just isn't working for me. I'm using Ecel 2016 but working of a 97-2003 workbook in compatability mode. My full code is below:

Sub Run_Monthly_Reporting()
'
' Run_Monthly_Reporting Macro
' jdioq dquihdui dygdoydw dygdw uiyegwd
'
' Keyboard Shortcut: Ctrl+m
'
    ActiveWorkbook.RefreshAll
    Columns("D:D").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("I:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=6
    Columns("M:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Total e-Comm Eng").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("LY e-Comm").Select
    Columns("A:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Desktop Data").Select
    Columns("A:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("A:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Desktop Eng").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("A:D").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Tablet Data").Select
    Columns("A:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Tablet Eng").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("e-Comm on Phone Data").Select
    Columns("A:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("e-Comm on Phone Eng").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Mobile").Select
    Columns("D:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Mobile Eng").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("LY Mobile").Select
    Columns("A:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("Worksheet").Select
    Range("A1").Select
    Application.CutCopyMode = False
    Cells.Replace What:=Chr(58), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="m:ar:brandedshop:", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:brandedshop:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="m:ar:shop:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:shop:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="m:ar:static:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:static:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="m:ar:buyersguide:", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:buyersguide:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:inspireme:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:events:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("K3:K1499").Select
    ActiveWindow.SmallScroll ToRight:=-4
    Range("F3:F1499").Select
    Selection.NumberFormat = "0"
    Range("I3:I1499").Select
    Range("I1499").Activate
    Selection.NumberFormat = "0.00%"
    Range("J3:J1499").Select
    Selection.NumberFormat = "$#,##0.00"
    ActiveWindow.SmallScroll ToRight:=5
    Range("M3:M1499").Select
    Range("M1499").Activate
    Selection.NumberFormat = "0"
    Range("N3:O1499").Select
    Selection.NumberFormat = "$#,##0.00"
    Range("E3").Select
    ChDir "C:\Users\williaki\Desktop"
End Sub

And an example of the colons is:

[TABLE="width: 415"]
<tbody>[TR]
[TD="class: xl106, width: 415"]halfterm:[/TD]
[/TR]
[TR]
[TD="class: xl106"]wow:[/TD]
[/TR]
[TR]
[TD="class: xl106"]valentines:[/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.
I ran the macro again to be certain I had not missed something. The macro, as posted, does remove all colons on all sheets, including the first sheet.

Had you changed something in the macro ?