Macro/VBA to find and replace over a range of cells in multiple identical files

[M__Payne]

Hello, wondering if someone can help me write a macro to find and replace all instances of "$C$214" with "$C$216". A few more details...
- I need to replace these values in the range E50:WG51 on worksheet "Monthly CF"
- The "Monthly CF" file is not the only worksheet in the file
- There are multiple (~150) identical files that I need to make the same change to (all files have the exact same worksheets with the exact same names)
- All files are located in the folder named "Proformas"
Note that:
- The "Monthly CF" sheet is protected (don't know if this matters to the code)
- Ideally I would like to re-protect this sheet before saving & closing the file

Any help on this would be much appreciated!

 

[gallen]

Hello and welcome.

Looping through all files in a folder isn't a huge ask. But need to clarify:

Your terminology is confusing.

Is "Monthly CF" a Sheet or a Workbook or both?

This code should point you in the right direction I'm assuming you have a working knowledge of VBA...

[/B]Sub DoChanges()


'UNTESTED. TEST ON COPY OF DATA


    Dim sFolderPath As String
    Dim sFileName As String
    Dim wb As Workbook, ws As Worksheet
    
    'change accordingly
    sFolderPath = "C:\Test\"
    
    'this will get all files of XLSM extension. Change where required
    
    sFileName = Dir(sFolderPath & "*.xlsm")
    
    'disable events and screen update
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    'loop through all files
    Do Until sFileName = ""
        'open workbook
        Set wb = Workbooks.Open(Filename:=sFolderPath & sFileName)
        'loop through all worksheets in the workbook
        For Each ws In wb.Worksheets
            'need to add exceptions here if you need to omit certain sheets
            ws.Range("E50:WG51").Replace What:="$C$214", Replacement:="$C$216"
        Next ws
        'close wb
        wb.Close
        'get next file
        sFileName = Dir
    Loop
    
    're-enable
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub


[B]

 

[M__Payne]

Thank you for your response!! Kicked me off to the right start. Sorry for the confusion, "Monthly CF" is a worksheet within the workbook. I needed to find/replace the values in the specified range only in the "Monthly CF" worksheet (and not others in the workbook). I ended up with the code below, note that I also needed to un-protect the subject sheet then re-protect all sheets with the password "Charlie". The code I used is below for anyone in the future that has the same problem!

Private Sub CommandButton2_Click()

Dim filenames As Variant
Dim i As Integer
Dim ws As Worksheet
Dim allsheets As Worksheet

Application.DisplayAlerts = False
Application.ScreenUpdating = False


filenames = Application.GetOpenFilename(MultiSelect:=True)

If IsArray(filenames) Then
For i = LBound(filenames) To UBound(filenames)

Set ws = Workbooks.Open(filenames(i), True, False).Worksheets("Monthly CF")
ws.Unprotect ("Charlie")
ws.Range("E50:WG51").Replace What:="$C$214", Replacement:="$C$216"

For Each allsheets In Worksheets
allsheets.Protect ("Charlie")
Next allsheets

ws.Parent.Close True

Next i
Else
MsgBox "did not work"
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub