Solved

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

Posted on 2012-03-19
6
480 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
Comment Utility
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
Comment Utility
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 41

Assisted Solution

by:dlmille
dlmille earned 137 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:JohnRobinAllen
Comment Utility
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 41

Accepted Solution

by:
dlmille earned 137 total points
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now