excel named range copy from sheet to sheet

6
votes

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!

1
excelnamed-ranges
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. - NickSlash
The simple way would be to create a copy of the existing sheet :) Else you will have to go down the VBA way - Siddharth Rout
"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? - rekordboy
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. - Tim Williams
That worked but will it copy over automatically from sheet to sheet or do I always have to hit ctrl+shift+enter? - rekordboy

1 Answers

3
votes

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.

enter image description here

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

enter image description here