PDA

View Full Version : change a string in each worksheet



lior03
03-26-2006, 01:00 AM
hello
i have a workbbok with many sheets. each sheet contains the month name.each month i have to submit a report.
i am trying to replace at once all previous month with the new month name.i do not want to repeat the action on each sheet.

Dim X, Y
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Y = InputBox("what to replace")
If Y = vbCancel Then Exit Sub
X = InputBox("with what")
If Y = vbCancel Then Exit Sub
Cells.Replace What:=Y, _
Replacement:=X, LookAt:=xlPart, MatchCase:=False
Next
Exit Sub


thanks

HaHoBe
03-26-2006, 02:07 AM
Hi, moshe,

if the names are identical for all the worksheets put both the Inputboxes outside the loop:

Dim X, Y
Dim ws As Worksheet
Y = InputBox("what to replace")
If Y = "" Then Exit Sub
X = InputBox("with what")
If X = "" Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Replace What:=Y, _
Replacement:=X, LookAt:=xlPart, MatchCase:=False
Next
If different names are used for the worksheets at least get the information from the previous inputbox:
Dim X, Y
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Y = InputBox("what to replace", , X)
If Y = "" Then Exit Sub
X = InputBox("with what")
If X = "" Then Exit Sub
ws.Cells.Replace What:=Y, _
Replacement:=X, LookAt:=xlPart, MatchCase:=False
Next
If the names are generated by formatting the date in MMM or MMMM this macro a different way must be use to achieve.

Ciao,
Holger