1

I am building a macro that should:

  • Copy a sheet (by tab name) from a model document into a destination document (achieved!)
  • Replace all references(NamedRange.RefersTo) to other books with the NamedRange value that formula outputs at copy moment.

So the concept is pretty simple...

The problem arises due to the model document having named ranges configured to other sheets in the model book.

The new sheet keeps the links to the copied document.

Name manager(Formulas Excel tab) in the new document

I want to know if it's possible to convert the formulas/named ranges into its calculated values(at the time of copy), making them static and final, and no references are carried along on the new copy.

Is this achievable in VBA?

Best Regards

Miguel Figueiredo
  • 13
  • 4
  • Converting formulas to values seems doable, yes. – BigBen Mar 20 '20 at 12:55
  • see if this helps ... https://stackoverflow.com/questions/35048469/copy-sheet-without-creating-new-instances-of-named-ranges .... and.... https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-avoid-duplicating-range-names-when-copying/3833db64-d36c-46cf-8dae-8de395161db2 – Naresh Mar 20 '20 at 13:05

0 Answers0