Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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.
0
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you could use a formula

=INDEX(Myrange,ROW(A1),COLUMN(A1))

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

=MyRange

and confirm with Ctrl-Shift-Enter

cheers, teylyn
0
 
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.
BBS-for-EE.xlsx
0
 
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 :)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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.
0
 
Patrick MatthewsCommented:
To do it the way you want, yes, I think VBA is required.
0
 
andrewssd3Commented:
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.
screenshot
0
 
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.
0
 
andrewssd3Commented:
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.
0
 
Saqib Husain, SyedEngineerAuthor Commented:
VBA is the only choice available which I do not intend to use.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now