Use Value of Adjacent Cell as Replace Value

[lindseyc]

I want each cell in Column A of Spreadsheet A to refer to cell E42 of its corresponding tab in Spreadsheet B. The name of the tab in Spreadsheet B to which a given cell in Column A should pull is contained in the directly adjacent cell in Column B. What I'm thinking to do is manually link the first cell, copy that link to the rest of the column, then do a find/replace macro that will replace the name of the tab to which the link refers with the name of the new tab. Therefore, I need this new tab name to be a variable that pulls the value from the adjacent cell. Below is the code I am trying to use, and it results in a couple seconds of thinking time, but no perceivable change in the selected cells:

Sub Provision()

Dim cell As Range, cellRange As Range, o As String

Set cellRange = Application.InputBox(Prompt:="Please Select List", Title:="List Select", Type:=8)

For Each cell In cellRange

o = ActiveCell.Offset(-3, 0).Value & "'"

Selection.Replace What:="AL-3H'", Replacement:= _
o, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False

Next cell

End Sub

 

[wigi]

I don't understand what you are after, but try this sample code:

Sub Provision()

    Dim cell As Range


    For Each cell In Application.InputBox(Prompt:="Please Select List", Title:="List Select", Type:=8) 'SELECT THE CELLS IN COLUMN A
        
        Workbooks("NAME OF THE SECOND SPREADSHEET.xlsx").Sheets(cell.Offset(, 1).Text).Range("E42").Copy
        With cell
            .Select
            .Parent.Paste link:=True
        End With


    Next


End Sub

 

[lindseyc]

A thousand kisses upon your forehead, a million roses upon your path, and a billion pennies in your vault. IT WORKED!!!

Thank you so much!

 

[wigi]

Thanks for the feedback !