Copying a named range

I have a named range at the top of my worksheet say 6 rows by 8 columns. I need to make a copies of this range as I move down the sheet, insert/delete rows from each copy and proceed down.

The problem is, where I need the new copy I do not wish to scroll to the top of the sheet, copy the range and then scroll back.

I also do not with to do a macro or split the window.

I remember in the days of lotus the copy command sequence was to issue the copy command, provide a "from" range and then a "to" range. I could do this from any location on the spreadsheet by typing range names or cell ranges. I wish to have such an option in excel but cannot find it.
LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?
Saqib Husain, SyedEngineerAuthor Commented:
Just what I was trying to avoid....moving around the sheet. Another of the few things I miss from Lotus123

Maybe I have to go into VBA after all...that I can do myself.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

you could use a formula


copy across and down as far as required.

Or, as an alternative, select a range of cells with as many rows and columns as the named range, then enter


and confirm with Ctrl-Shift-Enter

cheers, teylyn
Saqib Husain, SyedEngineerAuthor Commented:
Maybe you did not understand the problem. See an attached sample

The first block (named typ) which contains formulas and data needs to be copied and the data in the copy modified keeping the formulas. Your method would simply refer to the values in the typ range.

I may even need to insert rows as shown in the second block. I might even need to change the order or possible delete rows.

The basic requirement is to copy the given range to a new location and then work on the new range.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Patrick MatthewsCommented:
How about:

1) Select range to copy and hit Ctrl+C

2) Hit Ctrl+G for goto; enter range you want to go to

3) Hit Return to paste

The range you pasted to should also be the active selection at this point :)
Patrick MatthewsCommented:
To do it the way you want, yes, I think VBA is required.
You can use the named range dropdown box on the ribbon/toolbar for this.  If you select the named range you want an do ctrl-c or copy.  Then you can type the dest range into the box, e.g. H2:H8 and press enter.  Then right click and paste - this keeps the cut-copy mode active and you can do more copies by typing again in the box, pressing enter and right-click paste.  If you paste by pressing enter, the copy range is lost.
Saqib Husain, SyedEngineerAuthor Commented:
I do not want to drift away from my destination which keeps on changing otherwise I would have to remember where to get back. I want to be able to access the source range while staying at the destination range.
The only other suggestion I have is to open a New window on the same worksheet and split the display so you can always see the source range in one of the windows, and your working range in the other. Otherwise I really think you will have to resort to VBA.
Saqib Husain, SyedEngineerAuthor Commented:
VBA is the only choice available which I do not intend to use.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.