VBA Find/Replace Code not working

[arooney88]

Hi everyone. I'm having an issue regarding the below code. It is a macro that opens a workbook and basically find/replaces a piece of a formula in each cell and then saves to the next month's folder.

Everything appears to work fine. But when I open the newly saved workbook in the Oct folder, the find/replace function never actually worked, and the formulas still show previous month of /Sep/. I used the macro recorder to record most of this, but I'm wondering if this has something to do with it being selection.replace in the code? I tried cells.replace and that didn't work either.

Any help would be amazing! Thanks!

Sub createEastLADatabase()
'Creates East LA Database for next testing month
    
    Workbooks.Open Filename:= _
        "[URL="http://hcfg-portal.homestead.com/sites/serba/QA/QA%20Workshop/Databases/Sep/Jones%20East%20LA%20Database.xlsm"]Homestead | Get a site, Get found. Get customers.[/URL]" _
        , UpdateLinks:=0
    
    'unprotect Summary and Manager tab
    Sheets("Jones").Select
    ActiveSheet.Unprotect
    Sheets("Summary").Select
    ActiveSheet.Unprotect
    'Unhide all sheets
    
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.Visible = xlSheetVisible
    Next ws
    
    
    'Find/Replace for next month
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "'Oct 2014"
    
    [B]Selection.Replace What:="/Sep/", Replacement:="/Oct/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
[/B]   
    
    
    'hide all sheets except for Summary and Manager tab
    Sheets("Stefanie").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("John").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Sabrina").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Shona").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Oneika").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Jason").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Surekha").Select
    ActiveWindow.SelectedSheets.Visible = False
     Sheets("Susana").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("BU Codes").Select
    ActiveWindow.SelectedSheets.Visible = False
   
    
    'protect Summary and Manager tab
    Sheets("Jones").Select
    ActiveSheet.Protect
    Sheets("Summary").Select
    ActiveSheet.Protect
    
    'save to Sharepoint in new month's folder
    ActiveWorkbook.SaveAs Filename:= _
        "[URL="http://hcfg-portal.homestead.com/sites/serba/QA/QA"]Homestead | Get a site, Get found. Get customers.[/URL] Workshop/Databases/Oct/Jones East LA Database.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWindow.Close
 
 
End Sub

 

[XLfriend777]

Hi arooney88 - Rather than find and replace, which can be tricky, you might consider something like the code below. Hope this helps.

Sub arooney88_Set_Month_Year()
Dim Current_Month_Year As String
Dim Month_Year As String
Current_Month_Year = Date
Cells(1, 1).value = Current_Month_Year
'FORMAT Cell A1 as cumstom format mmm yyyy
'OR try this below
Month_Year = Format(Date, "mmm/yyyy")
Cells(2, 1).value = Month_Year
End Sub

 

[arooney88]

Thanks for the reply!

I actually went with using another For...Next loop that cycled through the worksheets and then ran the find/replace code.

For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Replace What:="/Sep/", Replacement:="/Oct/", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next ws

This worked for my issue and seemed pretty simple too.