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

M__Payne

New Member
Joined
Oct 3, 2019
Messages
2
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!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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...

Code:
[/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]
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top