I know this one is pretty dated, but i searched for an answer to similar issue and then found this. I needed to create a whole new year's worth of named ranges in a book but not delete/rename the last years. After pasting my current named ranges to a sheet i easily got the names renamed - they are all the same except the year. In any case, with 2 columns of new range names (col1) and their actual new ranges specified (col2) i tried to the code in #4 from Joe4 and only got the names applying to the cells specified in col2. Had to shut down and try again, tried different codes but nothing worked. Long story short, and really the reason for registering again here was that when i went back to the code from Joe4 and re-wrote this line: Set rng = Range(Mid(sh.Cells(r, "b"), 2)), bit by bit, using the tool tips (or whatever those boxes are called that show the format to use), and it worked a charm. I don't know why the copy and paste of the code didn't work - and VBA did not like that line until it was re-written.
So, i thought i would come and upvote this answer because it works exactly as explained. Thank you so much. I had 2 books that needed entry of 54 named ranges each, so i am a happy, happy geek right now.
