Solved

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

Posted on 2012-03-19
6
524 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:DaFranker
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 33

Assisted Solution

by:Norie
Norie earned 68 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 137 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
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!

 

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 137 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

679 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