excel named range copy from sheet to sheet

Question

I have an Excel question, I am wondering if there is an easy way to set up a clone or copy of a named range in one worksheet to another worksheet in the same workbook. I need this to be done automatically as the data comes in through a third party software tool. I am not an advanced excel user and was hoping there would be a simple way to do this without macros, vba code etc. Any thoughts? Thanks!

Drumlin

In theory yes this would be possible. I think VBA would be required though. Unless you don't use named ranges, and just use formulas in the target sheet that reference the data on the input sheet. – Saw

The simple way would be to create a copy of the existing sheet :) Else you will have to go down the VBA way – Issuance

"Unless you don't use named ranges, and just use formulas in the target sheet that reference the data on the input sheet." How would you recommend doing this with formulas? – Drumlin

If your named range 'blah' is on Sheet1, select a range of the same size on Sheet2 and type =blah in the formula bar, then Ctrl+Shift+Enter to create an array formula pointing to the other range. – Agonist

That worked but will it copy over automatically from sheet to sheet or do I always have to hit ctrl+shift+enter? – Drumlin

Answer 1

You can set the scope of the source named range to just the sheet, then copy the sheet. The named ranges will be copied as well.

To change the scope, use the Name Manager in Data section of the ribbon, or push Ctrl + F3. Select the Range and click edit.

To copy the sheet, right click on the sheet name and select "Move or Copy"

Cabbala

This doesn't work if you have a second named range that is defined in terms of a first named range within the same sheet. The copy of the second named range in the new sheet ends up being defined as =#N/A, rather than being copied and updated (as expected) to refer to the copy of the first named range. Super obnoxious. – Nuno