1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Copy paste cell into range based on another cell

Discussion in 'Business Applications' started by maxpowerdiaz, May 14, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. maxpowerdiaz

    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
     
  2. Sponsor

  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,352
    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?
     
  4. maxpowerdiaz

    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!
     

    Attached Files:

  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,352
    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.
     
  6. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    Thank you! I'm excited to see what you come up with.
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,352
    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.
     

    Attached Files:

  8. maxpowerdiaz

    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?
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,352
    My mistake :eek:

    I changed something and didn't test it afterwrds.

    Excuses :eek: :eek: :eek:

    Here's the corrected file.
     

    Attached Files:

  10. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43

    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!
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,352
    I added the button, the code is in the vba module.
     

    Attached Files:

  12. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    Fantastic! You are the man!
     
  13. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1098676