Find & Replace

[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

 

[Tim Otero]

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

 

[Biff]

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

 

[Mark]

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

 

[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