Replacing Sheet references within formulas with VB
 

Matt,

This is written with an activecell dependency: easy to re-write to do a variable line / range based
on other code (which you didn't post, so.....)

Dim myOldSheetName As String
Dim myNewSheetName As String

myOldSheetName = "XYZ"
myNewSheetName = "X Y Z"

If InStr(1, ActiveCell.Formula, "'" & myOldSheetName & "'") 0 Then
ActiveCell.EntireRow.Replace What:="'" & myOldSheetName & "'", _
Replacement:="'" & myNewSheetName & "'", _
LookAt:=xlPart
Else
ActiveCell.EntireRow.Replace What:=myOldSheetName, _
Replacement:="'" & myNewSheetName & "'", _
LookAt:=xlPart
End If


HTH,
Bernie
MS Excel MVP


"VBMatt" wrote in message
...
Hi,
I'm fairly new to VB and I have a worksheet which creates a summary of
information held on other worksheets.

I'm trying to write a Macro to take a user allocated name, add sheets into
the workbook and then add a line to the summary sheet with the references to
the correct sheet.

I have done the add sheet/add row/copy+paste info from row below, now I need
to replace the old sheet name in the formula with the new sheet name.

Any ideas how to do this as some of the sheets have spaces with the
reference 'X Y Z' and some don't, with the reference XYZ.

Open to suggestions on a better way to do this as well!

Cheers