Hyperlink formulas disappearing after a running code

[Kmanbozzy]

I have a spreadsheet with nearly 5000 defined names. We have a code that transfers filled out information from the template, to the most recent release of that template to capture any changes in sale prices or new items. So they are both the same spreadsheet except the most recent template has updates. Everyone uses their own template. The sales reps will transfer any information in their template to the new template with the most recent updates. Anyways, for some reason the hyperlinks that refer to different locations within the spreadsheet, are losing their formulas that show their text during a transfer. the links will still work, but the formulas are gone. the transfer code relies on looking at a portion of the named ranges to transfer information. the hyperlinks are not named, so the code shouldn't even be touching them, however the formulas are still disappearing. they all resemble something like this =IF(UP_I_SymTT_Qty>0,UP_I_SymTT_Ven,"") they only disappear after the code. i haven't a clue why this is happening. i have ran through my code to try and see where it this happens, and it happens at the end of what i will post, any one have any ideas?? are hyperlinks stored in the names collection or something??

i took out some of the code that didn't apply. and the end of the loop at the bottom is where i lose the formulas. the way the code works is the information is stored in the code with data() and then added to the new spreadsheet. there's an error handler but its only for cases where a name in the old is not in the new kinda of thing

Sub TransferToNew()


Dim wbFilledOut As Workbook     'workbook that has been filled out
Dim wbTemplate As Workbook     'workbook that is the latest Template
Dim s1 As Worksheet
Dim s2 As Worksheet
Dim s3 As Worksheet
Dim s4 As Worksheet
Dim s5 As Worksheet
Dim s6 As Worksheet
Dim s7 As Worksheet
Dim s8 As Worksheet
Dim R As Range


Set s1 = Sheet11 'ISEntry
Set s2 = Sheet74 'ContractReview
Set s3 = Sheet62 'Summary for Customer
Set s4 = Sheet64 'Step 1 - Base
Set s5 = Sheet79 'Step 3 - DocumentPower
Set s6 = Sheet81 'Step 5 - LTL
Set s7 = Sheet77 'Step 2 - Ancillary
Set s8 = Sheet84 'Appendix B
                


a = s1.Name
b = s2.Name
c = s3.Name
d = s4.Name
e = s5.Name
F = s6.Name
g = s7.Name
h = s8.Name


   'set the Filled Out/source book as the current active workbook
Set wbFilledOut = ActiveWorkbook


   'set the new, blank Template as the Target
Set wbTemplate = Workbooks("Investment Summary Template.xlsm")


Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False


Dim data(10000, 2)
Dim ECSdata(10000, 3)


wbFilledOut.Activate


wbFilledOut.Sheets(a).Select


i = 1
For Each nm In ThisWorkbook.Names
    If nm.Visible = True Then
        If InStr(nm.RefersTo, "#REF") > 0 Then
            nm.Delete
        Else
            If InStr(nm.RefersTo, Chr(34)) <= 0 Then
                If nm.RefersToRange.Parent.Name = a Then
                    If Range(nm).Locked = False Then
                        ii = 1
                        data(i, ii) = nm.Name
                        ii = 2
                        If Range(nm).HasFormula = True Then
                            data(i, ii) = Range(nm).Formula
                        Else
                            data(i, ii) = Range(nm).Value
                        End If
                        i = i + 1
                    End If
                Else
                    If nm.RefersToRange.Parent.Name = b Then
                        ii = 1
                        data(i, ii) = nm.Name
                        ii = 2
                        If Range(nm).HasFormula = True Then
                            data(i, ii) = Range(nm).Formula
                        Else
                            data(i, ii) = Range(nm).Value
                        End If
                        i = i + 1
                    End If
                End If
            End If
        End If
    End If
    TotalNames = i - 1
Next nm


wbTemplate.Activate
wbTemplate.Sheets(a).Activate
wbTemplate.Sheets(a).Unprotect "grand"


On Error GoTo errorspot


bnames = 0
For i = 1 To TotalNames
    Range(data(i, 1)) = data(i, 2)
    If 1 = 2 Then
errorspot:
        If badnames = "" Then
            badnames = data(i, 1)
            bnvalue = data(i, 2)
        Else
            badnames = badnames & ", " & data(i, 1)
            bnvalue = bnvalue & ", " & data(i, 2)
        End If
        bnames = 1
    End If
Next i
'after this loop, bye bye formulas :(

 

[yky]

Range(data(i, 1)) = data(i, 2)

Is there a formula in data(i,2)? If not, the formula is gone. You may want to copy the formula to an unused cell somewhere before executing this line and then copy it back.

<tbody>
</tbody>

 

[Kmanbozzy]

i figured it out. there were print areas in the defined names. they were not being filtered out, so when it came down to add the stored information at the point range(data(i,1)) = data(i,2) when the print area name came up and at was applied for some reason it made the formulas disappear. i have no idea why, but i filtered them out and it no longer gets rid of the formulas. so weird.