Excel - Copy paste cell into range based on another cell

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

maxpowerdiaz

Thread Starter
Joined
Aug 21, 2012
Messages
43
Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
Hi there,
Well, here we go again:
Excel version?
Your explanation does not paint a clear picture of what you want to achieve.
If you could attach a small sample file with non-sensitive / non-private data showing what you have and what you want to achieve it might help getting the idea.
The problem with these things is that you yourself already have the picture in your head and the hard part is to put it in 'writing' so that others see the same picture.
I imagine that macros will be no problem?
 

maxpowerdiaz

Thread Starter
Joined
Aug 21, 2012
Messages
43
I thought it might be a little confusing. What I'm trying to achieve seems fairly simple, but trying to explain it has proven to be difficult :) ....

Here is a simple sample. Here are some key points:

I only want to copy cell G3 from Sheet1.

I want the paste destination to be determined by the name in D3 from Sheet1.

I want the next entry for the same name to go below the last entry on Sheet2.

I want it to max at 20 entries for each name (i message box notifying max is reached would be great).

...Macros are no problem :) I'm currently working in Excel 2013, but i would need it to work in 2007 and 2010 also.
Thanks for your help!
 

Attachments

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
Okay, got the idea, and yes, it's not easy to explian but the file told me all.
I'll see if I can find a simple way with not too much code and get back to you.
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
I think this does it, it will allow you to add extra names in Sheet2 without needing to edit the vba code
Try it out.
It is only triggered if both a name and a nr are filled
Max 20 allowed per name.

No other prompts or message boxes in between.
Off to bed now.
 

Attachments

maxpowerdiaz

Thread Starter
Joined
Aug 21, 2012
Messages
43
Thank you for your hard work. But i am noticing an issue. Its not pasting the number in the right row. Instead of pasting it down on row 26, it pastes it in the first available row (If all rows are empty it will past it in 2nd row). It may be an easy fix but I havnt been able to figure it out?
 

maxpowerdiaz

Thread Starter
Joined
Aug 21, 2012
Messages
43
My mistake :eek:

I changed something and didn't test it afterwrds.

Excuses :eek: :eek: :eek:

Here's the corrected file.

It's working! Thanks for your hard work! But can i ask 1 more thing, how could i change this to work on a button instead of based on a worksheet change? Thanks again!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top