VBA to replace sheet reference in formula within specified range

Question

I'm not the most proficient with VBA but have managed to piece together the following actions:

Copy info from sheet in workbook that macro is built in

Paste info in different workbook sheet (serves as a template) and prompt input msg box to create new worksheet (copies template sheet and create new tab) with name entered in the input box. Code below for this step:

Function IsWorkBookOpen(name As String) As Boolean
    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(name)
    IsWorkBookOpen = (Not xWb Is Nothing)
End Function

Dim xRet As Boolean
xRet = IsWorkBookOpen("Roadmap - Campaigns - Current.xlsm")
    If xRet Then
    ThisWorkbook.Activate
    Range("B4:B20").Select
    Selection.Copy
    Workbooks("Roadmap - Campaigns - Current.xlsm").Sheets("Paste Request Form").Activate
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Else
    Workbooks.Open fileName:= _
        "C:\Users\CompanyName\Project - MM Team - MM Team\Roadmap - Campaign\Roadmap - Campaigns - Current.xlsm" _
        , UpdateLinks:=3
    ThisWorkbook.Activate
    Range("B4:B20").Select
    Selection.Copy
    Workbooks("Roadmap - Campaigns - Current.xlsm").Sheets("Paste Request Form").Activate
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    End If

Copy last row of data and insert new line with existing formulas/formatting into master tracking list that needs to update the formula sheet reference to the new sheet.

Dim NewSheet As String
NewSheet = InputBox("Please enter name for new worksheet")

Sheets("Paste Request Form").Select
Application.CutCopyMode = False
Sheets("Paste Request Form").Copy After:=Sheets(8)
ActiveSheet.name = NewSheet

Where I'm getting hung up is the last step. I cannot figure out how to update the sheet reference in the formula to the new sheet name from the input box. I've tried a couple things that I've found on the internet but not been successful to adapt them to work. Any help would be most appreciated!

There should be formulas to update in the newly inserted row of data. Example of the formulas with sheet reference below: =IF((VLOOKUP(C$3,'Tab 6.24.20'!$A:$B,2,FALSE))="","",(VLOOKUP(C$3,'Tab 6.24.20'!$A:$B,2,FALSE)))

I assume there has to be a way to define an object for the old worksheet string and replace it with the NewSheet string.

Forgive any misspoken terms, I'm self taught and not sure that I completey know all the appropriate vba terms.

So you want to replace "Tab 6.24.20" with the new name? You would benefit from reading stackoverflow.com/questions/10714251/… — SJR

Wizhi Wizhi · Accepted Answer · 2020-06-25T21:14:38

There are some reference like this one: Variable sheet name in sumif formula

So without testing, something like this:

Range("A6:A9").Formula = "=IF((VLOOKUP(C$3,'" & NewSheet & "'!$A:$B,2,FALSE))="","",(VLOOKUP(C$3,'" & NewSheet  & "'!$A:$B,2,FALSE)))"

Or if you want to search in a specific area for the formulas and replace the previous worksheet name with a new one.

It will search for values between the apostrophes ''. The sheetname 'Tab 6.24.20' fulfills this criteria and will therefore be replaced.

Range("A6:A9").Replace What:="'" & "*" & "'", Replacement:="'" + NewSheet + "'", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

VBA to replace sheet reference in formula within specified range

1 Answers