Re-define Names from 1 Excel Worksheet to another 1

[PWD]

I have come across a spreadsheet with a password protected worksheet with hidden columns. I’ve tried the following

Range("J:M").Select
Selection.EntireColumn.Hidden = False

But I get:-
Run-time error ‘1004’:
Unable to set the Hidden property of the Range class

Worksheets("OldName ").Unprotect

just prompts me for the password.

So I’ve just been able to copy the entire worksheet to a new one and I can get to the formulae etc. just fine. However all the Named Ranges are pointing at the original Worksheet and they change if I rename that worksheet. I’ve been having a good look through this forum to see if I can ‘Pick & Mix’ a solution from code “Out There” but without success.

What I want to do is to identify all the names (which are mostly multiple selections) in the Workbook and rename them, e.g. Prices, Refers to:

from =OldName!$G$11, OldName!$G$28, OldName!$F$44

to =Des!$G$11, Des!$G$28, Des!$F$44

I know there’s ActiveSheet.Names & ActiveWorkbook.Names but beyond that my ignorance knows no bounds!!

Help would be much appreciated.

Des.

 

[rmikesmith]

Here is a quick & dirty procedure to try. Add it to a module in the workbook where you have copied the worksheet in question.

Sub AdjustNamedRanges()
Dim Wks As Worksheet
Dim TmpName As Name
Dim NewRefersTo As String
Dim OldSheetName As String
Dim NewSheetName As String

   Set Wks = ThisWorkbook.ActiveSheet
   
   OldSheetName = "OldName"
   NewSheetName = "Des"

   With Wks
     For Each TmpName In ThisWorkbook.Names
       NewRefersTo = Replace(TmpName.RefersTo, OldSheetName, NewSheetName)
       TmpName.RefersTo = NewRefersTo
     Next TmpName
   End With
   
End Sub

Regards,
Mike

 

[PWD]

Dude!!!! This worked in a trice. Many thanks. Just what I NEEDED.

Des.