Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Find and Replace in one function | Excel Worksheet Functions | |||
Sum values in multiple sheets using Lookup to find a text match | Excel Worksheet Functions | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
Using Find and Replace All | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) |