Excel Macro help

[emu165]

Below is a part of a macro I can’t get to work. Basically, I copy in a worksheet from another file that contains formulas. The formulas are based on a create name range from the previous worksheet.
When I copy the worksheet into the current workbook, the formulas contain the workbook name. I am trying to change that to reference the worksheet name. The first SUM1 sheet is fine referencing the workbook name, but the second SUM2 tab has the “SUM2” worksheet name in the formula after the replace above. Sorry if this is confusing. I feel so out of my element and I’ve exhausted my google search. Thanks for any help!


'Copy incumbent summary into current workbook
‘bookName = current workbook name
‘wsdet = active detail sheet

Workbooks.Open Filename:="C:\data\med_incumbent.xlsx", UpdateLinks:=0
Sheets("Incumbent Par_NonPar Summary").Select
Sheets("Incumbent Par_NonPar Summary").Copy after:=Workbooks(bookName).Sheets(wsdet)

'Create name range on detail sheet
Application.DisplayAlerts = False
Worksheets(wsdet).Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.CreateNames Top:=True
'Application.DisplayAlerts = True

'Replace workbook name in formulas with detail sheet name with the defined name ranges
Sheets("Incumbent Par_NonPar Summary").Select
Cells.Select
Selection.Replace What:=bookName, Replacement:=wsdet, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

[SamT]

for Named Ranges:
For each Nm in Worksheets("SheetName").Names
Nm = Replace(Nm, OldString, NewStrng)

For Cell Formulas:
For each Cel in Worksheets("SheetName").Cells
Cel.Formula = Replace(Cel.Formula, OldName, NewName)

Tweak as needed

[snb]

Avoid 'Select' and 'Activate' in VBA.