VBA : 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:

[vba]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
[/vba]
How do I make sure I select all worksheets for any workbook i.e.e not using specific names in the [vba]Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select[/vba] line?

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

regards,

[Paul_Hossler]

To get you started

[vba]
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
[/vba]

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

[xluser2007]

Hi All,

I am trying to write a mcaro to create a vlaues only version of my active workbook. So in words the steps are (assuming the desired workbook is already open and active):

1. Create a copy of the Active workbook - call it say newvaluesworkbook in VBA.
2. In newvaluesworkbook. select all worksheets and globally value paste over their existing cells - thus making it values only.
3. All cells in newvaluesworkbook should have no highlighting i.e. Colorindex = Xlnone.
4. Set all tab colours in newvaluesworkbook to have "No Colour"
5. Delete all comments in newvaluesworkbook.
6. Save newvaluesworkbook with the same name as the Active workbook but append with "_values only". E.g. if active workbook is SpreadsheetD.xls, then newvaluesworkbook should be named as SpreadsheetD_values only.xls
7. Save the newvaluesworkbook workbook in the same directory as the activeworkbook, but in the Values folder. E.g If we have C:\SpreadsheetD.xls, then we want to create C:\Values\SpreadsheetD.xls. The values folder will exist for the active workbooks selected.

I tried to start Steps 3, 4 and 5, but even they seem to fall apart at each for loops with "Runtime erros 13 - debug mismatch". The program I wriote is as follows:

[vba]Sub create_Values_only_workbook()

Dim wbkwkshts As Worksheets
Dim wbkcomment As Comments

Application.ScreenUpdating = False

For Each wbkcomment In ThisWorkbook.Worksheets
wbkcomment.Delete
Next

For Each wbkwkshts In ThisWorkbook.Worksheets
wbkwkshts.Cells.Select
Selection.Interior.ColorIndex = xlNone
wbkwkshts.Tab.ColorIndex = -4142
Next

Application.ScreenUpdating = True

End Sub
[/vba]
If anyone could please anyone please guide me in writing the above, I would really appreciate it.

[xluser2007]

Please ignore the previous post, I accidentally pasted on an old thread.

The new thread is here:

http://vbaexpress.com/forum/showthread.php?t=18185

Sorry for the confusion.