How to copy named ranges from Sheet 1 to Sheet 2 and have their names update

[Course]

Hello, I have about 50 named ranges in Sheet 1(which is called Week 1). These names are named like this e.g Week1MondayWeight,Week1TuesdayWeight etc.

I want to copy these to Sheet 2(Week 2) and have their names update to Week2MondayWeight,Week2TuesdayWeight etc.

Is this possible and if so how can I do it.

Atm I am updating the names manually each time I copy to a new sheet and this is time consuming

Thank you

 

[tonyyy]

If you're okay with a vba solution, you might give this a try...

Sub CopyNamedRanges()

Dim RangeName As Name
Dim HighlightRange As Range
Dim RangeName2 As String
Dim NewRangeName As String

For Each RangeName In ActiveWorkbook.Names
    Set HighlightRange = RangeName.RefersToRange
    NewRangeName = Replace(RangeName.Name, "1", "2")
    RangeName2 = Replace(RangeName, "=Week1", "Week2")
    HighlightRange.Copy Destination:=Worksheets("Week2").Range(RangeName2)
    Range(RangeName2).Name = NewRangeName
Next RangeName

End Sub

The code copies each named range from Week1 and pastes it to the corresponding position in Week2, and renames the range by replacing the number 1 with the number 2.

Please note the reference to worksheets 1 and 2 as Week1 and Week2 (ie, without a space between Week and the #).

Cheers,

tonyyy

 

[Course]

Thanks for doing that up Tony, Ill try it later and let you know how I got on.

 

[Course]

Sorry for not getting back on this sooner.

Ive tried the Macro but it didnt work. I get an error message saying 400 anytime I run it.

Before I ran the Macro I highlighted the Whole Week 1 Sheet. Is this correct

 

[tonyyy]

How are the worksheets named? "Week 1" and "Week 2"; or "Week1" and "Week2". Should be the latter (no spaces).

 

[Course]

Hello Tony, the worksheets are named Week1 and Week2.

I have placed the code in "This Workbook" under Microsoft Excel Objects. I didnt place it directly in either the Week1 and Week2 sheets.

I tried running it after highlighting all of the Week1 worksheet and then tried running it without highlighting the sheet but got the same error message each time.

I am using Office 365. Under Macro settings on the Ribbon I have selected "Enable all Macros". There is another option to "Trust access to the VBA project object model". Should I select that also before running it.

Thanks for helping with this

 

[tonyyy]

Course,

The code should go into a regular/standard vba module, not the "This Workbook" module. There's a quick and easy tutorial at the Contextures website.

Highlighting or not highlighting anything will have no impact on the code.

Good that you selected "Enable all Macros." And yes, you also need to enable "Trust access to the VBA project object model."

Please let me know if you still get the error, in which case we'll add an error trap to try and identify the exact cause.

 

[Course]

I'm away from PC Atm but I'll enter that code correctly later and then I'll get back to you. Thanks again

 

[Course]

I ran the code and got a different error this time.

The error message said

"Run Time error '1004':
Application-defined or object-defined error"

 

[Course]

Sorry I mistakingly said the named ranges are named like this "Week1MondayWeight,Week1TuesdayWeight etc". They are named like this Wk1MondayWeight,Wk1TuesdayWeight etc.

I changed your code to the code below and ran it. The part I changed is in red. I got the same error message as above ""Run Time error '1004':
Application-defined or object-defined error"

Sub CopyNamedRanges()

Dim RangeName As Name
Dim HighlightRange As Range
Dim RangeName2 As String
Dim NewRangeName As String

For Each RangeName In ActiveWorkbook.Names
Set HighlightRange = RangeName.RefersToRange
NewRangeName = Replace(RangeName.Name, "1", "2")
RangeName2 = Replace(RangeName, "=W1", "Wk2")
HighlightRange.Copy Destination:=Worksheets("Week2").Range(RangeName2)
Range(RangeName2).Name = NewRangeName
Next RangeName

End Sub

<tbody>
</tbody>

<tbody>
</tbody>