VBA code for copying worksheets containing named ranges from source book to destination

Question

I have 2 books. Source book and assignment workbook. They are completely identical, with the exception of one sheet that has the same name in both but different data (both contain about 30 sheets). What I wanted to do was copy the remaining identical worksheets from the source workbook to the destination workbook, leaving 1 sheet in it that stores the data.

In principle, identical worksheets present in the assignment workbook should be replaced by those specified in the source workbook. Worksheets contain formulas and named ranges. I was successfully able to write VBA code to copy worksheets. But since the named ranges have the volume of a workbook. The named ranges still refer to source book locations. So I get 2 named ranges with the same name. Sort of:

'The one already present in the destination workbook (from the worksheet which was replaced) Name=VaccStart , Refers To =Sheet2!$A$2 'The one due to the copied worksheet. Name=VaccStart , Refers To =[C:\Users\.....\Source.xls]Sheet2!$A$2 

I want named ranges to refer to the target book, not the source book when I copy them. Since all the sheets in both books are the same, and I just replace them.

Answer 1

One easy way to get around inadvertently creating links when moving from a source book to a destination is to move the destination book from the source to itself.

Screenshot for xl2010

• Change .... Links
• "Change source" and select the current file as the new source

Answer 2

This will change the named ranges to remove the link to the external file:

 Sub ResetNamedRanges() Dim nm As Name Dim sRefersTo As String Dim iLeft As Integer Dim iRight As Integer For Each nm In ActiveWorkbook.Names sRefersTo = nm.RefersTo iLeft = InStr(sRefersTo, "[") iRight = InStr(sRefersTo, "]") If iLeft > 1 And iRight > 0 Then sRefersTo = Left$(sRefersTo, iLeft - 1) & Mid$(sRefersTo, iRight + 1) nm.RefersTo = sRefersTo End If Next nm End Sub