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
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
Code:
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 :(