Macro for Copying from one sheet to pasting in another sheet.

[John1975]

Hi Team,

I need a urgent help with macro. I have attached a sample excel file here.
- I have two sheets "Mapping" and URLs
- These two sheets may have multiple rows. I guess, while creating a macro, it should count till end of rows.
- In sheet "Mapping", I need to copy content in "A2" and find it in Sheet "URLs" column "H" and replace the with the value of "B2" - (Mapping sheet)
- This need to repeat till all items completes in "Mapping" sheet.

I hope it is clear, if not I can clarify further.

 

[Chihiro]

- In sheet "Mapping", I need to copy content in "A2" and find it in Sheet "URLs" column "H" and replace the with the value of "B2" - (Mapping sheet)

So... the code should replace string found in Column H of URLs that match Column A value of Mapping with Column B value of the Mapping?

Meaning it's not copying, but replacing existing string in column H of URLs. Is this correct?

 

[John1975]

Yes, just replacing part of Url in column "H"

 

[Chihiro]

Something like below then.

Sub Demo()
Dim lstRng As Range
Dim cel As Range
With Sheets("Mapping")
    Set lstRng = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
With Sheets("URLs").Range("H:H")
    For Each cel In lstRng
        .Replace What:=cel.Value, Replacement:=cel.Offset(, 1).Value
    Next
End With

End Sub

 

[John1975]

Wow! It worked.... Thanks a lot for all your help and support. Really appreciate it