Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2012-03-19
6
514 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to recover unsaved document deleted from a recycle bin. 7 41
Turn date into age 17 33
Word 2016 - View side-by-side displays docs vertically 2 21
V-Lookup 11 19
Let’s list some of the technologies that enable smooth teleworking. 
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

861 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