How to replace list A to list B across sheets

Dylan

Board Regular
Joined
Jul 27, 2007
Messages
150
Good Day!

I have created the code with marco, but it only work perfectly when I run the marco in the active sheet but not on other sheet.
Please help.

Here is my file's background.
1. I have a table in sheet1, column B to be replace with column A.
2. I have 3 others sheet, ie. M1, M2 & M3 which content datas
3. I would like to replace all the items listed in the table across M1, M2 & M3 with a marco.

Dylan
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi,
I think I know what you need - can you post your macro and we can try to help. Its hard to tell without seeing your attempt
 
Upvote 0
Hi, here is the code. Thank for helping.

Code:
Private Sub CommandButton1_Click()
update "Sheet1"
End Sub

Sub update(TxtLocation)

lastrow = Worksheets(TxtLocation).Range("A65536").End(xlUp).Row
For I = 1 To lastrow
ReplaceTarget = Worksheets(TxtLocation).Cells(I, 2).Value
ReplaceWith = Worksheets(TxtLocation).Cells(I, 1).Value
ReplaceAll ReplaceTarget, ReplaceWith
Next I

End Sub

Sub ReplaceAll(ReplaceTarget, ReplaceWith)
    Sheets(Array("M1", "M2", "M3")).Select
    Sheets("M1").Activate
    Cells.Replace What:=ReplaceTarget, Replacement:=ReplaceWith, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub
 
Upvote 0
Still don't quite understand what result you want - maybe this?

Sub ReplaceAll(ReplaceTarget, ReplaceWith)
Dim SheetList(3)
Dim i As Integer

SheetList(0) = "M1"
SheetList(1) = "M2"
SheetList(2) = "M3"

For i = 0 To 2

Sheets(Array("M1", "M2", "M3")).Select
Sheets(SheetList).Activate
Cells.Replace What:=ReplaceTarget, Replacement:=ReplaceWith, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Next i
End Sub
 
Upvote 0
Sorry, let me explain more clearly. I have 2 column in sheet1.
Column A
ACB
CDE
CDE
EFG
EFG

Column B
ACB Eng
CDE Eng
CDE Cor.
EFG Eng
EFG Cor.


I have 3 sheets which contents Data with customer's name as in column B.
I would like to have a button in sheet1 which will replace the customer's as below within 3 sheets.
ACB Eng with ACB
CDE Eng with CDE
CDE Cor. with CDE
EFG Eng with EFG
EFG Cor. with EFG
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top