Excel Tips - Find and Replace across the whole workbook

I've a workbook approaching the 10Mb mark, which is causing speed & therefore
usability issues over our network. It consists mainly of links & formulae (I
have already deleted all the macros). Has anyone got any tips or ideas on
how to reduce the size of the file?

I wanted to find and replace the word AAA with the word BBB on all worksheets in my workbook.

Should I use the "Selection" arguement or should I be using something else?


Selection.Replace What:="AAA", Replacement:="BBB", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Thanks
Sam

I recorded the macro below, which replaces any instance of "B3!" to "B2!" in the entire workbook (find and replace, find what: B3!, replace with: B2!, within: workbook)

Sub ChangeB3_To_B2()
'
' ChangeB3_To_B2 Macro
'

'
Cells.Replace What:="B3!", Replacement:="B2!", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

I tweaked the macro a bit to the code below, but when I ran it I didn't do what I expected. I don't think it's searching through the whole workbook. I think it's just looking at the current sheet. Can someone let me know what I'd need to change? Thanks

Sub ChangeTabs()
If Sheets("Template").Range("I18").Text = "Y" Then
MsgBox "Y"

Cells.Replace What:="B3!", Replacement:="B2!", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If Sheets("Template").Range("I18").Text = "N" Then
MsgBox "N"

Cells.Replace What:="B2!", Replacement:="B3!", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If



End Sub

I have a macro that finds and replaces the value in cell C7 on Sheet1 with the value in cell B7. The find/replace function defaults to replace only within the sheet. There are 16 sheets that have values to be replaced so I want it to replace 'within WORKBOOK' not 'within SHEET'. However, I can't find a macro setting to tell it to replace all values within the whole workbook. Can someone tell me how? Thanks.

My code is:
Cells.Replace What:=Trim(Range("C7")), Replacement:=Trim(Range("B7")), LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Morning all,

I have a set of 15 properties. I run a report for each property that has a significant amount of data within. On top of that I have a separate summary workbook that references these workbooks. Right now I am in the building stage for this. What I'd like to do is go to tab one link everything to the appropriate outside spreadsheet then create a copy of the spreadsheet, do a find and replace and set up the next 14 properties that way. Unfortunately When I do my find and replace it wants me to find the appropriate workbook for every cell. Any ideas?

I would appreciate any help that can be provided.

Chris

OK,
I need to find a value in a column and replace it with a corresponding value in anther workbook.
so if the value in the first column in the first workbook is x i need it to look in the second workbook for x and return what is in column 2 in the same row? Perhaps i need to use a vlookup?

Does anyone know of a macro that will:
1) open a Find & replace window and
2) the "within" "Workbook" automatically set? TIA. Steve

Title pretty much says it all....

I have to change drive letters in all the links in 200+ worksheets. A Find & Replace that can do it in one go sure would be helpful... is there any trick that might help me out?

I need to create a "Monthly Summary" (in it's own workbook) from many
workbooks on a monthly basis. Each source workbook contains monthly tabs.
The January Summary is linked to each source workbook, cell by cell (for
totals). Rather than do this manually each month, I would like to copy the
January summary worksheet, and then do a "find and replace" - in other words,
just replace the "word" January with the word February, so my new summary
goes and retrieves the totals from the February worksheets. Data in both
source worksheets are in the same cells.

There's got to be a way. Any suggestions. Thanks so much

Yes, another homework for you guys. I have found the code to open the "Find and Replace" dialogue box but is it possible to open it with "within:" Workbook instead of Sheet?

This is the code that open up the dialogue box

Code:

Sub FineMe()
        Application.CommandBars("Edit").Controls("Find...").Execute
        
End Sub

You know, there are some lazy people that I have to dealt with who can't be bother of clicking on "Workbook" to find what they want.

Thank you in advance