Find, Copy and Paste across all worksheets

Maklal

New Member
Joined
Jul 17, 2020
Messages
4
Office Version
2013
Platform
Windows
Hi, I'm really need help with this. Been trying to achieve this for the last 2 days.
On my first worksheet, I have Values in column A and column B there are also values but they are hyperlinks.
I'm trying to find the cells with the values from A across the rest of my worksheets and and replace them with the values from column B (and still have them hyperlinked when the cells are replaced).

I found some code from another Thread that's close to doing this but I haven't been able to modify it correctly so far to achieve this.


VBA Code:
Sub ReplaceInAllShts()
'Average Joe

    Dim Cnt As Long
    Dim Replc As Variant
    Dim Sht As Long
  
    With Sheets(1)
        Replc = .Range("A1", .Range("B" & Rows.Count).End(xlUp)).Value
    End With
    
    For Sht = 2 To Worksheets.Count
        For Cnt = 1 To UBound(Replc)
            Sheets(Sht).UsedRange.Replace what:=Replc(Cnt, 1), Replacement:=Replc(Cnt, 2), _
                LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
        Next Cnt
    Next Sht
    
End Sub
If someone can help me, I would extremely appreciate it.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,930
Messages
5,508,184
Members
408,669
Latest member
AgsikapAko

This Week's Hot Topics

Top