find replace in multiple sheets based on namebox
 

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

find replace in multiple sheets based on namebox
 

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



On Dec 19, 3:57 pm, "gabitzu" wrote:
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!

gabihttp://fotoblog.ro

find replace in multiple sheets based on namebox
 

Thank you!

It works very fast!

You saved me from a long copy paste:)

gabi

http://fotoblog.ro


ankur wrote:
Hi ,

try the following macro....