find replace in multiple sheets based on namebox

[gabitzu]

Hi!

I have a strage situation:

- around 50 excel files;
- each excel file with 200 sheets;

In each sheet I have to cell with the same content:

cell A35 has content: =A34+1
and the cell A40 has content =A34+1

I want to find replace in all sheets from one workbook and search for
A40 and replace the conten with: =A39+1

How can I do it?

I searched a lot on internet before to ask and no answer...

Thank you!

gabi
http://fotoblog.ro

 

[ankur]

Hi ,

try the following macro....



Sub test()

Dim FS As FileSearch
dim i as interger
Set FS = Application.FileSearch

FS.LookIn = "C:\Windows\... put your folder name here"

FS.FileType = msoFileTypeExcelWorkbooks
FS.SearchSubFolders = True
FS.Execute

For i = 1 To FS.FoundFiles.Count
Workbooks.Open (FS.FoundFiles(i))

For Each Sheet In ActiveWorkbook.Sheets
Sheet.Range("A40").Formula = "=A39+1 "
Next Sheet

ActiveWorkbook.Close (True)
Next i

End Sub

Regards
Ankur Kanchan
www.xlmacros.com

 

[gabitzu]

Thank you!

It works very fast!

You saved me from a long copy paste

gabi

http://fotoblog.ro