Macro only works in debug mode

[All2Cheesy]

Hello all,

I'm attempting to run a macro over multiple workbooks which clean up, and add additional data into each workbook.
For the most part, this macro runs correctly, however, one section will only run properly if stepped through in debug mode. If run normally it will have no effect.

What I am trying to do is replace any instance of the date 01/01/2020, with '1-1. Below is the section of code which is not running correctly.

Rows("1:1").Select
    Cells.Replace What:=CDate("1/01/2020"), Replacement:="'1-1", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Is there a way to force excel to execute this section of the macro? Any assistance is greatly appreciated.

For reference, here is the full macro.

Sub ProcessFiles()

'Disable screen updating
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .EnableEvents = False
        .DisplayAlerts = False
    End With

'***********************************************************************Overwrite Rate Files***********************************************************************
'Declare variables
    Dim Filename, Pathname As String
    Dim wb As Workbook

'Locate file path to be changed
    Pathname = ActiveWorkbook.Path & "C:\Test\"
    Filename = Dir(Pathname & "*.CSV")
    
'Run DoWork sub - loop for all files
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoWork wb
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop
    
'Enable screen updating
    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
    End With
    
End Sub

Sub DoWork(wb As Workbook)
    With wb

'***********************************************************************Create Melbourne 2 In Rate Files***********************************************************************

'Replaces incorrect header formats
'Replace 1-1 date error
Rows("1:1").Select
    Cells.Replace What:=CDate("1/01/2020"), Replacement:="'1-1", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

'Replace blanks
    Selection.Replace What:="", Replacement:="0-0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

'Applies a filter to data to only show cons receiveing into Melbourne
    ActiveSheet.Range("$A:$AS").AutoFilter Field:=3, Criteria1:= _
        "Melbourne"
        
'Applies a filter to data to only show a kilo charge
    ActiveSheet.Range("$A:$AS").AutoFilter Field:=8, Criteria1:= _
        "Kilogram"
                
'Selects first cell after header row
    Range("A2").Select
    
'Selects all used cells underneath cell A2
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
'Copies selected cells
    Selection.Copy
    
'Selects first available blank cell
    Columns("A:A").Select
    Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select
    
'Pastes copied cells into selected cell
    ActiveSheet.Paste
    
'Replaces any instances of Melbourne with Melbourne 2 in selected range
    Selection.Replace What:="Melbourne", Replacement:="Melbourne 2", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

        
'Removes filters
    ActiveSheet.ShowAllData
    End With
End Sub

 

[rlv01]

What happens if you get rid of the 'select' and use this instead:

    Rows("1:1").Replace What:=CDate("1/01/2020"), Replacement:="'1-1", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False

 

[All2Cheesy]

What happens if you get rid of the 'select' and use this instead:

    Rows("1:1").Replace What:=CDate("1/01/2020"), Replacement:="'1-1", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False

It still won't run when running the sub, but continues to work when debugging.

 

[rlv01]

As an experiment, add some delay around the file open and file save code.

        'Run DoWork sub - loop for all files
   Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        Application.Wait (Now + TimeValue("0:00:02"))
        DoWork wb
        wb.Close SaveChanges:=True
        Application.Wait (Now + TimeValue("0:00:02"))
        Filename = Dir()
    Loop

If that works, then try

    'Run DoWork sub - loop for all files
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoEvents                                      '<--
        DoWork wb
        wb.Close SaveChanges:=True
        DoEvents                                      '<--
        Filename = Dir()
    Loop

 

[DanteAmor]

Try this

    .Sheets(1).Rows("1:1").Replace What:=CDate("1/01/2020"), Replacement:="'1-1", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

When you check the result open the file with the notepad.
If you open the csv file with excel, it automatically converts 1-1 to 01/jan/2020

 

[All2Cheesy]

As an experiment, add some delay around the file open and file save code.

        'Run DoWork sub - loop for all files
   Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        Application.Wait (Now + TimeValue("0:00:02"))
        DoWork wb
        wb.Close SaveChanges:=True
        Application.Wait (Now + TimeValue("0:00:02"))
        Filename = Dir()
    Loop

If that works, then try

    'Run DoWork sub - loop for all files
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoEvents                                      '<--
        DoWork wb
        wb.Close SaveChanges:=True
        DoEvents                                      '<--
        Filename = Dir()
    Loop

Thanks rlv, unfortunately, adding a delay doesn't seem to work.

 

[All2Cheesy]

Try this

    .Sheets(1).Rows("1:1").Replace What:=CDate("1/01/2020"), Replacement:="'1-1", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

When you check the result open the file with the notepad.
If you open the csv file with excel, it automatically converts 1-1 to 01/jan/2020

Thanks Dante, this looks like it's the issue. Closing, and re-opening the file reverts the 1-1 to 01/Jan/2020.

Is there a way to ensure a CSV keeps the 1-1 formatting?

 

[All2Cheesy]

Thanks for your assistance everyone, I believe I've got it working.

As Dante mentioned, saving the file as a .CSV automatically altered the format of 1-1 to 1-Jan.

In order to get around this, I amended the formula in the offending cell to '="1-1"

'Replace 1-1 date error
    Rows("1:1").Replace What:=CDate("1/01/2020"), Replacement:="'=""1-1""", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False

 

[DanteAmor]

Glad to hear it works for you. Thanks for the feedback.