+ Reply to Thread
Results 1 to 9 of 9

VBA code to copy and paste range based on separate cell value

  1. #1
    Registered User
    Join Date
    08-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    VBA code to copy and paste range based on separate cell value

    Hi, I'm working on a database and looking for a code: I want it copy if cell A2 is 1 I want it to copy the values of cells T48:X48 then transpose them and paste them onto a separate sheet under (below) 1 or whatever the value of what A2 contains. It should do the above only in the sheet that also corresponds to the value of cell G8. So if value in G8 on the original sheet is "gear" it should paste the values of T48:X48 in sheet "gear" under the value similar to that of cell A2.

    I have made the values of A2 and G8 drop down lists. I want this Code to run every time any of these values are changed.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code to copy and paste range based on separate cell value

    Hi Mhofu,

    Try this:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA code to copy and paste range based on separate cell value

    Hi Mhofu and welcome to the Forum.

    If you wish this to happen
    I have made the values of A2 and G8 drop down lists. I want this Code to run every time any of these values are changed.
    You'll need to use Worksheet Event Code. Post a sample of your file (including your drop-downs and any code you have). I'm certain one of us can help.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Registered User
    Join Date
    08-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: VBA code to copy and paste range based on separate cell value

    Daily Delivery Analysis v4.xlsxHi thanks for the reply I had written something with a layman's code but can't seem to find it on. Anyway here is the attachement

  5. #5
    Registered User
    Join Date
    08-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: VBA code to copy and paste range based on separate cell value

    Hi I know this is layman but perhaps might give an idea of what I am trying to do
    Please Login or Register  to view this content.
    Last edited by Cutter; 08-31-2012 at 10:49 PM. Reason: Added code tags

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code to copy and paste range based on separate cell value

    Hi Mhofu,

    Put this in "Weekly Analyis":

    Please Login or Register  to view this content.

    Or maybe this one:

    Please Login or Register  to view this content.
    Last edited by xladept; 08-31-2012 at 08:25 PM.

  7. #7
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: VBA code to copy and paste range based on separate cell value

    @ Mhofu

    Welcome to the forum.

    Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
    Thanks.

  8. #8
    Registered User
    Join Date
    08-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: VBA code to copy and paste range based on separate cell value

    Hi Xladept and thanks for the code. However it sticks on the second line and I can't seem to figure out what is wrong with it

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code to copy and paste range based on separate cell value

    Hi Mhofu,

    Change the target addresses to absolute reference:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1