edit replace

T

Tee

The company that I work for has recently changed their name, we have
literally thousands of spreadsheets with the old name on. Is there any way
that I can do edit replace over a whole file?
 
P

Peter Rooney

In Excel 2003, there's an option to specify the whole workbook when doing a
search and replace. This would get around having to S&R on each worksheet in
a workbook. Otherwise, you might have to write some VBA Code. As far as
S&Ring over multiple workbooks, i'd suggest you DEFINITELY need some VBA code!

Regards

Pete
 
T

Tee

I take it this would be fairly complex?

Peter Rooney said:
In Excel 2003, there's an option to specify the whole workbook when doing a
search and replace. This would get around having to S&R on each worksheet in
a workbook. Otherwise, you might have to write some VBA Code. As far as
S&Ring over multiple workbooks, i'd suggest you DEFINITELY need some VBA code!

Regards

Pete
Click to expand...
 
P

Peter Rooney

Well, the first thing is, do you have the "Whole Workbook" option in Search
and Replace in your version of Excel?

How many workbooks are we talking about?

Are they all in the same folder, or in different folders?

Pete
 
D

Dave Peterson

Since you're doing Edit|replace, you should be able to group the worksheets you
want changed.

Rightclick on a worksheet
choose "Select all Sheets"
select all the cells
(use that little button to the left of the column headers and above the row
numbers)

Edit|Replace
replace all

And remember to ungroup the sheets
right click on any of the worksheet tabs and choose ungroup sheets.

Be aware that this won't touch your headers/footers.

And Jan Karel Pieterse's FlexFind offers this capability, too:
http://www.oaltd.co.uk/MVP/
 
T

Tee

Hi Peter

Yes i do have the workbook option in my version of excel, and we are
probably talking about 100 or so spreadsheets, all in different folders...
 
G

Gord Dibben

Tee

Just curious.....How did you get from "literally thousands of spreadsheets"
down to 100 or so?

Liberal weeding?


Gord Dibben Excel MVP
 
Top