VBA code to change sheet reference in formula

[nina212001]

I'm not the most proficient with VBA but have been doing a lot of googling to create one. What I've created is VBA code to insert and prompt a title for the new worksheet. I have formulas on a specific worksheet linked to various other worksheets and I need my new row (copy/inserted by the macro already) to change the sheet reference to the new worksheet. Each time the worksheet name will vary because the macro is taking the last row of data and copying/inserting new row after. Formula reference below:
=IF((VLOOKUP(C$3,'Club 6.24.20'!$A:$B,2,FALSE))="","",(VLOOKUP(C$3,'Club 6.24.20'!$A:$B,2,FALSE)))

How do I set the VBA to recognize the old worksheet name and insert the new worksheet name?

 

[Zack Barresse]

You can do a Replace on the formula. As long as it's the only sheet reference in the formula it's fairly straight forward. Below is an example of how I'd look at doing something like this.

Sub testFoo()
    ReplaceSheetReferenceInFormulas ActiveSheet.Range("A1:A3"), "Sheet 2"
End Sub


Sub ReplaceSheetReferenceInFormulas(ByVal ReplaceRange As Range, ByVal NewSheetName As String)
    
    Dim Sheet As Worksheet
    Dim NewSheet As Worksheet
    Dim NamePositionStart As Long
    Dim NamePositionEnd As Long
    Dim OldSheetName As String
    
    On Error Resume Next
    Set NewSheet = ThisWorkbook.Worksheets(NewSheetName)
    On Error GoTo 0
    If NewSheet Is Nothing Then
        'new sheet name not found in this workbook
        Exit Sub
    End If
    
    For Each Sheet In ThisWorkbook.Worksheets
        NamePositionStart = InStr(1, ReplaceRange.Cells(1, 1).Formula, Sheet.Name, vbTextCompare)
        If NamePositionStart > 0 Then
            OldSheetName = Sheet.Name
            If InStr(1, OldSheetName, " ", vbTextCompare) > 0 Then
                OldSheetName = "'" & OldSheetName & "'"
            End If
            Exit For
        End If
    Next Sheet
    
    If NamePositionStart = 0 Then
        'no sheet reference found in formula
        Exit Sub
    End If
    
    NewSheetName = Trim(NewSheetName)
    If InStr(1, NewSheetName, " ", vbTextCompare) Then
        If Left(NewSheetName, 1) <> "'" Then
            NewSheetName = "'" & NewSheetName
        End If
        If Right(NewSheetName, 1) <> "'" Then
            NewSheetName = NewSheetName & "'"
        End If
    End If
    
    ReplaceRange.Replace What:=OldSheetName, Replacement:=NewSheetName, LookAt:=xlPart
    
End Sub

 

[nina212001]

@Zack Barresse Thanks for your help! I wasnt' able to adapt that to work with my existing code. I probably should have shared this detail before.

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

1. Copy info from sheet in workbook that macro is built in
2. 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

3. 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 know all the appropriate vba terms.

 

[Zack Barresse]

Thanks for the additional information. It helps immensely. There are still some questions needed to provide you with a solution.

In your code above (2), you activate ThisWorkbook, then reference a Range. What worksheet is that Range object on? The code will currently assume the active sheet. Better to specify it.

Is the range B4:B20 the range with formulas in it you want to copy? It looks like you're copying that range from one worksheet (not sure which one) to "Paste Request Form", then copying that sheet. Can you confirm this is the range with formulas you want to update? If so, I'd like to confirm you want the references to be in the newly created worksheet.

Not a question, but look at your If/Then statement. The only difference between the clauses is the file is opened. This means that the code is duplicated and superfluous. There is also no check to see if the file is opened by you or not, which really only plays into matters if you want to close it if found that way, which is what I usually do. Your requirement might be to open the file via code and then leave it open, not sure.

 

[nina212001]

Thanks again for your help! I'd been stumped for a while on this. See my additional comments, hopefully I captured everthing clearly enough. Can't thank you enough!

ThisWorkbook is the workbook that owns the macro, this is a form that I'm copying info from into a different workbook opened by the code, if not already open. ThisWorkbook is a single sheet workbook and the Range("B4:B20") is the info to be copied from this workbook to a different workbook.

Range("B4:B20")is not the range where I'm trying to replace sheet reference in the formula.
The range where I'm trying to replace sheet reference in the formula is in workbook "Roadmap - Campaigns - Current.xlsm" which was opened by the code. The sheet is titled "Campaign Tracking" (which I realize now wasn't a part of anything I provided you before, sorry) and I'm trying to select the last row of data and replace formulas in that last row to reference the NewSheet that is created from the input box code

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

As for the IF/THEN statement, if I understand correctly, is the below the better code?

Dim xRet As Boolean
xRet = IsWorkBookOpen("Roadmap - Campaigns - Current.xlsm")
If xRet Then
Else
Workbooks.Open fileName:= _
"C:\Users\CompanyName\Project - MM Team - MM Team\Roadmap - Campaign\Roadmap - Campaigns - Current.xlsm" _
, UpdateLinks:=3
 End If

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

 

[Zack Barresse]

No worries. We'll get you sorted. Let me see if I have this straight - you have a workbook (ThisWorkbook) which contains a single worksheet. You want to open the Roadmap file, if not already open, and copy the data from [ThisWorkbook] range B4:B20 of the only worksheet, and paste it onto a new worksheet in range B7. This new sheet will be created in the Roadmap file defined by a name the user provides at runtime (via inputbox).

Is that close?

My main question revolves around this statement:

I'm trying to select the last row of data and replace formulas in that last row to reference the NewSheet that is created from the input box code

Where is this last row of data found? It's unclear how you would find this.

 

[nina212001]

The range B4:B20 is not actually pasting into the new worksheet. It's pasting into sheet called "Paste Request Form", my original 3rd bullet. The "Paste Request Form" has a lot of additonal fields (which I'm using as sort of a template), so I'm copying that sheet into the NewSheet.

The last row of data is on a different worksheet called "Campaign Tracking" where each row references different tabs. The code should copy & insert a new row, which then becomes the last row and I want to update the references to the NewSheet. Hope that makes sense

 

[Zack Barresse]

Where is the last row found? Which columns? Is there any data below it? Confirming, the 'Campaign Tracking' sheet is located in the 'Roadmap' file, yes?

 

[nina212001]

Yes, the 'Campaign Tracking' sheet is in the 'Roadmap' file. The last row will change each time this runs, in previous code, I'm always copying the last rown and inserting a new row below, which is the one to link to the 'NewSheet'. Thanks!

 

[Zack Barresse]

Where is the last row found? Which columns? Is there any data below it?