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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Makrini

Well-known Member
Joined
May 22, 2007
Messages
1,035
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

Dylan

Board Regular
Joined
Jul 27, 2007
Messages
150
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

Makrini

Well-known Member
Joined
May 22, 2007
Messages
1,035
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

Dylan

Board Regular
Joined
Jul 27, 2007
Messages
150
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,191,366
Messages
5,986,238
Members
440,012
Latest member
StumpedGump1987

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
Top