Simple? mass find/replace for all tabs/files in a folder.

[SandsB]

The macro below was written by somebody smarter than me. It deletes the contents (not the rows) of rows 3 through 50 for every tab in every file in E:\Reports\temp. Saved me a million man hours. I need something similar - something that will replace every occurrance of 2020 with 2021 in every tab in every file in the same folder. There's text in cells and thousands of links that point to 2020 files. All needs to be updated to 2021. I can do it manually by opening each file, selecting all the tabs together and doing one find/replace. But....there's way too many files for that.

What can I replace this line with? ws.Rows("3:50").ClearContents

-----
Sub BlankIt()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim directory As String
directory = "E:\Reports\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(directory)
For Each file In folder.Files
Workbooks.Open directory & Application.PathSeparator & file.Name
For Each ws In Sheets
ws.Rows("3:50").ClearContents
Next ws
ActiveWorkbook.Close True
Next file
Application.ScreenUpdating = True
End Sub

 

[Candyman8019]

Not sure if I have all of the syntax correct, but likely something like this should work:
ws.Worksheet.Replace _ What:="2020", Replacement:="2021", _ SearchOrder:=xlByColumns, MatchCase:=True

 

[SandsB]

I'm sure somethig like this would. But not this.

 

[Candyman8019]

Sub ReplaceAll()
Dim sh As Worksheet
Dim fnd As Variant
Dim rpl As Variant

fnd = "2020"
rpl = "2021"

For Each sh In ActiveWorkbook.Worksheets
sh.Cells.Replace what:=fnd, Replacement:=rpl, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sh
End Sub

 

[SandsB]

Sub ReplaceAll()
Dim sh As Worksheet
Dim fnd As Variant
Dim rpl As Variant

fnd = "2020"
rpl = "2021"

For Each sh In ActiveWorkbook.Worksheets
sh.Cells.Replace what:=fnd, Replacement:=rpl, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sh
End Sub

If I know my VBA, and I don't, doesn't this just work on one file that's open and not all files in a folder?

 

[Candyman8019]

The intent was for you to try and merge the two scripts to make one that works

Try replacing the row you initially identified with:
ws.Cells.Replace what:="2020", Replacement:="2021", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

 

[SandsB]

Works great. Thank you everybody

 

[Syntank868]

Thank you for this, saved me a few dozen hours of work easily