macro apply to all worksheet
 

hi,
i have a simple macro to do find and replace but only on the sheet that i
open.
may i know how to make this macro to change all sheets if found the same
string in the workbook?

sub macro()
Cells.Replace What:="C:\xxx\xxx\xxx\", Replacement:="C:\yyy\yyy\yyy\" _
, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat _
:=False, ReplaceFormat:=False
end sub

thanks

macro apply to all worksheet
 

For intSheet = 1 To ActiveWorkbook.Sheets.Count
Sub Macro()
With ActiveWorkbook.Sheets(intSheet)
'Place you code here
..Cells.Replace What:="jacob", Replacement:="jac" _
, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat _
:=False, ReplaceFormat:=False

End With
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"AskExcel" wrote:

hi,
i have a simple macro to do find and replace but only on the sheet that i
open.
may i know how to make this macro to change all sheets if found the same
string in the workbook?

sub macro()
Cells.Replace What:="C:\xxx\xxx\xxx\", Replacement:="C:\yyy\yyy\yyy\" _
, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat _
:=False, ReplaceFormat:=False
end sub

thanks

macro apply to all worksheet
 

Hi,
Thanks for the reply but still only the open sheet is updated only.
I still need to open sheet by sheet to run only can get changed.
I have 50 over sheets and I cannot open every single sheet to get it updated.
Any more advice?
Thanks


"Jacob Skaria" wrote:

For intSheet = 1 To ActiveWorkbook.Sheets.Count
Sub Macro()
With ActiveWorkbook.Sheets(intSheet)
'Place you code here
.Cells.Replace What:="jacob", Replacement:="jac" _
, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat _
:=False, ReplaceFormat:=False

End With
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"AskExcel" wrote:

hi,
i have a simple macro to do find and replace but only on the sheet that i
open.
may i know how to make this macro to change all sheets if found the same
string in the workbook?

sub macro()
Cells.Replace What:="C:\xxx\xxx\xxx\", Replacement:="C:\yyy\yyy\yyy\" _
, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat _
:=False, ReplaceFormat:=False
end sub

thanks

macro apply to all worksheet
 

I have tried this with 3 sheets and it works for all 3 even if i am in
Sheet1. Note that there is a period just before cells (.Cells). Paste the
code as such and try changing the text to be searched and replaced.

If this post helps click Yes
---------------
Jacob Skaria


"AskExcel" wrote:

Hi,
Thanks for the reply but still only the open sheet is updated only.
I still need to open sheet by sheet to run only can get changed.
I have 50 over sheets and I cannot open every single sheet to get it updated.
Any more advice?
Thanks


"Jacob Skaria" wrote:

For intSheet = 1 To ActiveWorkbook.Sheets.Count
Sub Macro()
With ActiveWorkbook.Sheets(intSheet)
'Place you code here
.Cells.Replace What:="jacob", Replacement:="jac" _
, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat _
:=False, ReplaceFormat:=False

End With
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"AskExcel" wrote:

hi,
i have a simple macro to do find and replace but only on the sheet that i
open.
may i know how to make this macro to change all sheets if found the same
string in the workbook?

sub macro()
Cells.Replace What:="C:\xxx\xxx\xxx\", Replacement:="C:\yyy\yyy\yyy\" _
, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat _
:=False, ReplaceFormat:=False
end sub

thanks