VBA code to change sheet reference in formula

[brett1again]

Hello,
I have a copy row to another sheet macro that I'm using which works fine but when the row is moved to another sheet the sheet reference in one of the formulas is still referencing the name of the sheet it was moved from which causes an error. I'm needing a code that'll help resolve this.
Here is the formula and the reference sheet I'm needing to change after the copy is "Cambridge"

=SUMPRODUCT(ISNUMBER(SEARCH('Formatting Lists'!$A$1:$A$11,Cambridge!U2))*'Formatting Lists'!$B$1:$B$11)+IF($V2>250,($V2-250)*1.5,0)+($W2*8.5)

Here is the worksheet macro I'm currently using

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Oldvalue As String
  Dim Newvalue As String
  Dim xRng As Range
  Dim Lastrow As Long
  
  If Target.Count > 1 Then Exit Sub
  
  On Error GoTo Exitsub
  Set xRng = Range("U:U").SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  
  If Not Application.Intersect(Target, xRng) Is Nothing Then
  
    Application.EnableEvents = False
    
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    Target.Value = Newvalue
    
    If Oldvalue <> "" Then
      If Newvalue <> "" Then
      
        If Oldvalue = Newvalue Or _
          InStr(1, Oldvalue, ", " & Newvalue) Or _
          InStr(1, Oldvalue, Newvalue & ",") Then
          Target.Value = Oldvalue
        Else
          Target.Value = Oldvalue & ", " & Newvalue
        End If
        
      End If
    End If
        
    Application.EnableEvents = True
    
  End If
  
  '********Delete record
  If Not Intersect(Target, Range("AD:AD")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Lastrow = Sheets("cambridge completes").Cells(Rows.Count, "AD").End(xlUp).Row + 1
    
    If Target.Value = "CLOSE" Then
      Rows(Target.Row).Copy Destination:=Sheets("cambridge completes").Rows(Lastrow)
      Rows(Target.Row).Delete
    End If
  End If

Exitsub:
End Sub

Thanks for the help

 

[Zot]

Your problem related to this?

VBA code to change sheet reference in formula

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...

www.mrexcel.com

 

[brett1again]

I read that forum the other evening and it seemed like the same problem I'm having for the most part. I wasn't sure how to adapt the code for my needs though

 

[Zot]

I read that forum the other evening and it seemed like the same problem I'm having for the most part. I wasn't sure how to adapt the code for my needs though

Try this. It will ask for text you want to change From and To. It will search all formula in the sheet and replace any matching text.

Sub ChangeReference()

Dim strA As String, strFrom As String, strTo As String
Dim cell As Range
Dim ws As Worksheet

strA = InputBox("Enter string to change From and to in this format" & vbLf & "From|To")

strFrom = Split(strA, "|")(0)
strTo = Split(strA, "|")(1)

Set ws = ActiveWorkbook.Sheets("Sheet1")  ' Change sheet name if necessary

For Each cell In ws.Cells.SpecialCells(xlCellTypeFormulas)
    Debug.Print cell
    cell.Formula = Replace(cell.Formula, strFrom, strTo)
Next

End Sub