multi-tab replace in Excel VBA - how?

  madwab 11:27 14 Dec 04
Locked

I can select multiple tabs in Excel and do a find/replace that works across all of them. In VBA it only works on the first selected.
So given two sheets, XX and YY, how do I change all a's to b's? What's wrong with this?

Sheets(Array("XX", "YY")).Select
Cells.Replace What:="a", Replacement:="b", LookAt:=xlPart

(I wondered if I needed to activate all of the selected tabs, but changing 'Select' to 'Activate' gives an error; presumably only one sheet can be active.)

  Simsy 14:34 14 Dec 04

just to see waht the code is. I haven't done the replace bit, but it seems you have the line correct, but then need to activate the first sheet...

This is the code I got;

Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet1").Activate

then the action code.


Hope this helps,

Regards,

Simsy

  Simsy 14:39 14 Dec 04

I've just added some "a"'s to both sheets in a workbook, in differerent places on both sheets, and run the following;

Sheets(Array("Sheet1", "Sheet2")).Select

Sheets("Sheet1").Activate

Cells.Replace What:="a", Replacement:="b", LookAt:=xlPart


and it worked fine. (Win98se, Excel 2000)

Good luck,

Regards,

Simsy

  madwab 13:58 30 Dec 04

(Sorry about the delay - I've been away.)

I've copied your code from the posting and pasted it directly into a macro, and it doesn't work for me! That's with WinXP, Excel 2000...

????

  Simsy 15:45 30 Dec 04

Do you get an error message?

I'm not sure I can help any further, but more info may enable someone else to help.


(I've just tried it here at work, (Excel XP, Win2000), and it doesn't work properly... only first sheet changes. This implies it's an OS thing... but that doesn't quite ring true with me!

Good luck,

Regards,

Simsy

  madwab 11:04 31 Dec 04

...I'd have to agree it seems unlikely. Nope, I don't get any errors. Just for completeness, I created a new spreadsheet with 3 tabs and some 'a's in each, then recorded a macro while highlighting all 3 tabs and changing 'a's to 'b's. They change on all 3 tabs, but if I hit undo and run the macro, they only change on the first tab. The code produced is

Sub Macro1()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Cells.Replace What:="a", Replacement:="b", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
End Sub

It's pretty much the same code... maybe I'll try it on Office 2003 tonight.

Happy New Year.

The following might assist and can be modified to do less than the total of worksheets, but in that event they would have to be consecutive sheets.

++++++++++++++++++++++++++++++++

Sub ReplaceText()

For Ws = 1 To Worksheets.Count

Sheets(Ws).Activate

Cells.Replace What:="a", Replacement:="b", LookAt:=xlPart

Next Ws

End Sub

++++++++++++++++++++++++++++++++

HTH

Gordon

  madwab 07:43 04 Jan 05

...more or less what I'm using, in lieu of of proper solution. It just seems odd that VBA, which is there to enhance what can be done in Excel, can't do something that can be done in Excel.

  madwab 13:45 06 Jan 05

I put this topic up on MrExcel's forum and didn't get an answer there either, but I got a lead with which I worked out the solution. It goes like this:

Excel allows you to select a range on sheet1 and a different range on sheet2; or if you highlight both sheets and select a range, it selects the range on both sheets. So far, so good. BUT if you select a range on sheet1 and a different range on sheet2 and THEN select both sheets, [i]the range on the last selected sheet is applied to both sheets[/i]. That is not what I would expect.

With that starting point, it's straighforward to show that Excel will find and replace anything on all sheets [i]in the selected range[/i]. The final caveat is that if no range is selected, then the default 'apply to the whole sheet' range applies only to the first sheet!!!

The solution is simply, therefore, to add 'Cells.Select' before the 'Selection.Replace'. Still a little tacky, but far better than a loop - I've tried it, and it works.

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

These are the Best Christmas Ads and Studio Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…