8

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!

rekordboy
  • 146
  • 1
  • 1
  • 5
  • 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 Apr 04 '13 at 23:23
  • 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 Apr 04 '13 at 23:25
  • 1
    _"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 Apr 04 '13 at 23:42
  • 3
    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 Apr 04 '13 at 23:53
  • 1
    That worked but will it copy over automatically from sheet to sheet or do I always have to hit ctrl+shift+enter? – rekordboy Apr 05 '13 at 00:07

1 Answers1

5

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

wilsjd
  • 2,178
  • 2
  • 23
  • 37
  • 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. – Keith F. Kelly May 17 '23 at 03:26