0

I currently copy a worksheet that contains workbook and worksheet-specific named ranges. The worksheet-specific references need to be the way they are, the workbook ones need to be hardcoded. I am looking for a way to fixate every named range that contains an external reference.

My current code looks like this, but it's not really what I am after:

Sub HardcodeValuesInExternalNamedRanges(wb As Workbook, ws As Worksheet)
Dim namCur As Name

For Each namCur In wb.Names
    If (InStr(1, namCur.RefersTo, ThisWorkbook.Name) > 0) Then
        namCur.RefersTo = "=" & ws.Evaluate(namCur.RefersTo)
    End If
Next namCur
End Sub

I could not find a way to fixate the values properly. The way it is done now, does the job to some degree but I would prefer if the values were properly converted within the cell and not just on a named range basis.

Another option would be to loop through all cells and see if it contains a named range, but I think this would be too time consuming.

Is there a way to do it more efficiently?

Spurious
  • 1,903
  • 5
  • 27
  • 53

1 Answers1

1

Rather than loop through the cells, how about looping a .FindNext like this?

Do
    rng.Value = rng.Value 'Hardcodes the value
    Set rng = .FindNext(What:=ThisWorkbook.Name Lookin:=xlFormulas) 'finds the next Value to be hardcoded
Loop While Not Rng Is Nothing
nwhaught
  • 1,562
  • 1
  • 15
  • 36
  • I think I solved it in a different manner, or rather circumvented it but your solutions seems superb. I will not be able to test it as the workbook is already doing what I want it to do, but thank you nonetheless and maybe someone else will find it useful. – Spurious Mar 24 '15 at 16:57