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

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
617
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 13, 2007
Messages
617

ADVERTISEMENT

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

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,315
Messages
5,641,482
Members
417,211
Latest member
loadius

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
Top