External references...convert to values

[theta]

Hi. I have macro that copies a sheet ("Section 1") to a new workbook.

Only problem is that it contains vlookups etc to other sheets in the original workbook.

So I need a macro that will go through and change each external reference (linking back to the old sheet) as values

But still have other formulas on the same sheet (=sum etc) that can remain as formulas...just need to external ones to be made values

?

 

[theta]

Hi

Yes tried your last one...works great! thanks

@Glenn there are 2 different requirements that are now overlapping

In this post I wanted to delete external links

I made a seperate post for deleting the actual named ranges that have external elements.

Formula element now works great, thank Njimack

Still not sure how to do this, don't want #REF named ranges sat in the new workbook?

 

[jim may]

Here's a shoot in the dark...

Sub Foo()
For Each C In ActiveSheet.UsedRange
    If C.HasFormula Then
        For Each Rn In ActiveWorkbook.Names
                If InStr(1, C.Formula, Rn, 1) > 0 Then
                ' Do nothing
                End If
        Next Rn
    Else
    If InStr(1, C.Formula, "[", 1) > 0 Then C.Formula = C.Value

    End If
Next C
End Sub

 

[theta]

Thanks all, sorted now!

 

[jim may]

Better attempt..

Sub Foo()
For Each C In ActiveSheet.UsedRange
    If C.HasFormula Then
        For Each Rn In ActiveWorkbook.Names
                If InStr(1, C.Formula, Rn.Name, 1) > 0 Then
                ''' Do nothing
                MsgBox "Found one"
                Else
                If InStr(1, C.Formula, "[", 1) > 0 Then C.Formula = C.Value
                End If
        Next Rn
    End If
Next C
End Sub

 

[theta]

Have tried combining this also, but does not work

Should delete any named range with a link to an external workbook?

Public Sub ClearExternalNameRanges(Optional ByVal CurrentBook As Workbook = Nothing)
    Dim lngIndex As Long
    If CurrentBook Is Nothing Then
        Set CurrentBook = ActiveWorkbook
    End If
    With CurrentBook
        For lngIndex = .Names.Count To 1 Step -1
            If InStr(LCase$(.Names(lngIndex).RefersTo), "*.xls*") > 0 Then
                .Names(lngIndex).Delete
            End If
        Next lngIndex
    End With
End Sub

 

[njimack]

Again, it looks like you're over-complicating things. I would go with something like this (untested)

Sub ClearExternalNamedRange()
Dim nm As Name

For Each nm In ThisWorkbook.Names

    If InStr(1, nm.RefersTo, ".xls") > 0 Then nm.Delete

Next nm
End Sub

 

[theta]

Still doesn't work!

Both workbooks are open at the same time and both remain open, hence using the .xls method

Doesn't remove them though?

Then when I close the original workbook (which would not happen practically) they jump to values #REF with the Refer To still pointing to the original workbook

 

[theta]

As we are looking at the link property (rather than the value) should we be using something like Range(nm).Parent.Name

RangeName.RefersToRange.Parent.Name <> ActiveSheet.Name Then RangeName.Delete ?

 

[theta]

UPDATE : I have found that if I create an external link in the original workbook, this will be deleted

This is not ideal. I want references in the secondary workbook to be deleted (the workbook the tabs are being copied to)

START > Workbook 1 is open
STEP 1 > Section 1 copied to new Workbook 2
STEP 2 > Links broken
STEP 3 > Try to remove .xls named ranges (but it only deletes them form the start workbook 1 not this workbook 2).

Workbook 2 is defined within the main sub a 'owb' but trying to run a macro passing owb as Worksheet has no effect?

Dim owb as Workbook
Set owb = Workbooks(Right(sXLfile, Len(sXLfile) - InStrRev(sXLfile, "\")))

---------------------------------------------

Private Function Section1(owb As Workbook)
'Purpose: Create Section 1 of HUB Invoice
ThisWorkbook.Sheets("Section 1").Copy Before:=owb.Sheets("HUB Agreements>")

If DoesWorkSheetExist("Section 1", owb) Then
frmWait.lbxInfo.AddItem "Section 1 copied"
Section1 = True
End If

'Set dyanamic references and formats to values

Call ClearExternalNamedRange(owb)

---------------------------------------------

Public Sub ClearExternalNamedRange(owb as Workbook)
Dim nm As Name

For Each nm In owb.Names

If InStr(1, nm.RefersTo, ".xls") > 0 Then nm.Delete

Next nm
End Sub