Using Dynamic Named Ranges Created From VBA

0
votes

I have created a series of Dynamic Named Ranges using VBA by creating a copy of a sheet that already contains a series of dynamic named ranges and then renaming the ranges that are automatically generated in the new sheet. All of the new ranges show up in the name manager window Required Named Range. However, I can't seem to use any of the ranges in excel functions. Desired Functions. Any Suggestions?

Cheers, Rob

1
vbaexcel
You may need to recalculate the cell that calls the named range – Scott

1 Answers

0
votes

I worked it out. When a sheet is copied the scope of all the dynamic ranges are local to that sheet not the workbook and so can not be used on other sheets even after changing the range names.

I found this code that changes all local scopes to workbook on a given sheet.