Select all workbooks and Global FIND/ REPLACE

[xluser2007]

Hi All,

I am trying to write a relatively simple macro, but don;t know how to generalise it.

basically in a workbook, I want to select all worksheets (i.e. 'Shift + Tab' from first to last worksheet).

Then Select the entire worksheet (i.e. end up highlighting all possible active cells in the workbook).

Then do a simple replace of a number 200710 with 200711 (it is actually a folder reference that will change links faster than edit links if the workbooks are open).

I opened up a workbook and recorded the following macro:


Sub selectallwbks_then_Global_FINDreplace()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Cells.Select
Selection.Replace What:="200710", Replacement:="200711", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

How do I make sure I select all worksheets for any workbook i.e.e not using specific names in the
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select line?

Is there any way to do this simpler than the above process?

regards,

[Paul_Hossler]

To get you started



Sub selectallwbks_then_Global_FINDreplace()
Dim wb As Workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each wb In Application.Workbooks
For Each ws In wb.Worksheets
Application.StatusBar = "Replacing in " & wb.Name & " -- " & ws.Name
ws.Cells.Replace What:="200710", Replacement:="200711", LookAt:=xlPart
Next
Next
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub


Not perfect, for ex. if a sheet is protected it won't work, but you can add tests for that. Will also run on any non-visible workbooks like add-ins. Again you can add checks for those

Paul

[xluser2007]

Hi Paul,

thanks for your help. that is a very nice (generalised) macro.

As for sheet protection and other such technical matters, it is not really an issue for my excerice, but thanks for alerting me to this.

I also like the Statusbar tip, wasn't aware of this one prior to this mail.

Always so much to great stuff to elarn learn!

Thanks :friends: