Find & Replace

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Fellows

I have 500, yes 500 individual workbooks, all identical in
layout.
I want to find,replace and delete items that are common to
all sheets. The items are all at the same cell locations
on each sheet. Is there a way to do this without opening
all sheets individually.

Regards
Michael
 
Hi Michael,

Right click on one of the tabs and select "Select all sheets." Do an
Edit|Replace, as you normally would (to delete, leave the "replace with"
empty). Then, right click on one of the sheets and "Ungroup," if needed.

HTH
tim
 
Hi Michael,

500 workbooks or 500 worksheets in 1 workbook? If it's the
latter, right click on any sheet tab and click on Select
all sheets. Now anything you do will be done to *ALL* 500
worksheets. If it is 500 *WORKBOOKS* - Good Luck!

Biff
 
You copy below code to modul workbooks and start macro in
active worksheet. In this code is example with replace
format specific cells.

Sub find_replace()
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Select

'code replace eg:

Range("A1").Select ' cell locations
Selection.NumberFormat = "#,##0.00" ' change cells

Next i
End Sub

HTH
Mark
 
This is solve of this problem.
Open all necessary workbooks and copy code to any workbook.
Change command code replace and start macro.

Sub find_replace()
Dim i As Integer
Dim oWbk As Workbook

For Each oWbk In Application.Workbooks

oWbk.Activate
For k = 1 To Sheets.Count

Sheets(k).Select

'code replace eg:

Range("A1").Select ' cell locations
Selection.NumberFormat = "#,##0.00" ' change cells

Next k
Next oWbk
End Sub

Regards Mark
 
Back
Top