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: 553
  • Last Modified:

With Excel, copy a group of cells and paste them into multiple cells without the mouse

I have an Excel 2010 spreadsheet in Windows 7. Cells B-1 to C-3 have data and are selected (total 6 cells). I want to copy those cells with Ctrl + c  and then paste them with Ctrl + v into multiple cells below, say to a selection of Cells B-4 to C-200. It appears that about two thirds of the time when I paste what I copied, only one instance of the six cells gets entered. How can one consistently get the paste to repeat the data until all the selected cells are filled?

A kludge is not to copy the cells but to drag the lower right corner of the selected cells down to cell C-200. Is there any way to do the same thing with the keyboard rather than the mouse?

Thanks!
j.r.a.
0
JohnRobinAllen
Asked:
JohnRobinAllen
3 Solutions
 
Frank WhiteCommented:
Unsure whether changes in 2010 have removed this functionality (or broken it, which is also possible), but in 2002/2003 and 2007, holding SHIFT and using arrow keys (or ctrl+arrow, or any other method of moving the cursor to multi-select a range of cells I know of, for that matter) to select your target range and then Ctrl+V pasting will "paste to repeat until filled".

Note that I've only gotten this to work reliably if either the same amount of columns are selected (and thus it copies downwards repeating by rows) or the same amount of rows are selected (and thus rightwards by columns). Selecting less rows than the source, less columns, or more of both, will produce unexpected and varying results.
0
 
NorieData ProcessorCommented:
Try extending the destination range down to row 201 (or up to 198), when it's 200 the copied range won't 'fit' properly.

Then copy B1:C3, goto the Name box to the left of the formula bar, enter B4:C201 and then CTRL+V.
0
 
dlmilleCommented:
The problem (and it is consistent across Excel 2003+) is that the cells you select before hitting CTRL-v (paste) are not a multiple of the rows being copied.

So, you have to do the math in your head to catch the multiple.  E.g.,

Copying B1:C3 (that's 3 rows) would necessarily require you to select a multiple of 3 rows.  So, when you select B4:C200 you'll note the little popup hovering by your mouse as you select that range, "197R x 2C".  197 is not divisible by 3, so you need to add 1 row, or go back 2 rows.  The "correct" selection for it it paste properly would be B4:C201, or B4:C198.

Cheers,

Dave
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JohnRobinAllenAuthor Commented:
I believe that imnorie's suggestion is the answer and that the reason I was successful in pasting only a third of the time was that I was by accident selecting a multiple of 3 only a third of the time.

I'll test it out a few more times and will get back to you. Meanwhile another problem has arisen. If it does not solve itself, I'll post a question on it. The problem is that when I used the right-click and drag solution, it froze the worksheet and rendered it useless.

That is all the more reason to hope that imnorie's solution works consistently.

Back again soon!
jra
0
 
dlmilleCommented:
As I stated, if you're copying 3 rows, you must paste a multiple of 3.  If its 5 rows, then a multiple of 5, etc.

Dave
0
 
JohnRobinAllenAuthor Commented:
The solution is perfect. Thanks to both experts. Now my only problem is how to calculate the end point of the selection for pasting when I've selected fourteen or some other odd number of rows to copy.
      Let's see: 200 / 14 = 14.285, so I better bump the multiple up to 15. 15 times 14 = 210, so I should extend the selection to that row. Fortunately I am using Excel so I don't have to do those calculations in my head. I'm glad this problem did not arise in Word.
     Thanks for the help
     j.r.a.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now