Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2012-03-19
6
Medium Priority
?
555 Views
Last Modified: 2012-03-19
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
Comment
Question by:JohnRobinAllen
6 Comments
 
LVL 3

Expert Comment

by:Frank White
ID: 37738513
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
 
LVL 37

Assisted Solution

by:Norie
Norie earned 272 total points
ID: 37738558
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
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 548 total points
ID: 37738584
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 

Author Comment

by:JohnRobinAllen
ID: 37738604
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
 
LVL 42

Accepted Solution

by:
dlmille earned 548 total points
ID: 37738621
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
 

Author Closing Comment

by:JohnRobinAllen
ID: 37739748
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

Apart from its culture, demographics, and beliefs, Indian infrastructure is also quite complex, especially because of the challenge of overpopulation that stares us right in the eye. Due to this, it has been quite difficult to anticipate the traffi…
Nowadays Bates Stamping is used in many legal firms, lawyers, etc. because adding bates numbers at header or footer of your documents helps in the unique identification of the pages. Identify best legal Bates Stamp Software and insert Bates numbers …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question