PDA

View Full Version : VBA : select all workbooks and Global FIND/ REPLACE



xluser2007
02-07-2008, 08:08 PM
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
02-07-2008, 08:40 PM
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
02-07-2008, 11:31 PM
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:

xluser2007
03-05-2008, 06:16 PM
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):
Create a copy of the Active workbook - call it say newvaluesworkbook in VBA.
In newvaluesworkbook. select all worksheets and globally value paste over their existing cells - thus making it values only.
All cells in newvaluesworkbook should have no highlighting i.e. Colorindex = Xlnone.
Set all tab colours in newvaluesworkbook to have "No Colour"
Delete all comments in newvaluesworkbook.
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
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:

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

If anyone could please anyone please guide me in writing the above, I would really appreciate it.

xluser2007
03-05-2008, 06:20 PM
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.