+ Reply to Thread
Results 1 to 7 of 7

Macro to copy value across sheets, using another sheet as index

  1. 12-08-2016, 03:40 PM #1
    Lemmy Kickit
    Lemmy Kickit is offline
    Registered User
    Join Date
    06-12-2016
    Location
    Ann Arbor, MI
    MS-Off Ver
    2010
    Posts
    6

    Macro to copy value across sheets, using another sheet as index

    Hi all, I've got a head scratcher that I feel shouldn't be very difficult yet here I am, well, scratching my head.

    I need a macro to match a value in Sheet3 Column A to Sheet2 Column A, find the corresponding value in Sheet2 Column B, then match THAT value to Sheet1 Column A, then write the appropriate value in Sheet3 Column B to Sheet1 Column B. OK, allow me to illustrate:

    Sheet1
    State # (value written to this column)
    AZ
    CA
    CO
    FL
    GA
    IL
    IN
    MA
    MI
    MN
    NY
    OH
    OR
    PA
    TN
    TX
    WA

    Sheet2
    City State
    Philadelphia PA
    Seattle WA
    Houston TX
    Los Angeles CA
    Dallas TX
    Portland OR
    Kansas City MO
    Chicago IL
    Denver CO
    Atlanta GA
    San Francisco CA
    Detroit MI
    Baltimore MD
    Austin TX
    San Diego CA


    Sheet3
    City #
    Atlanta 24
    Baltimore 52
    Chicago 84
    Dallas 35
    Houston 86
    Los Angeles 67
    New York 76
    San Francisco 35

    So basically Sheet1 should be the sum totals of each of the states, but first it needs to loop through Sheet2 as the index to match the state with the city. I feel like this is so simple yet nuanced enough to elude me, so if anyone can help, I would sincerely appreciate it. I'm working with an example doc with the exact information provided if that helps. Please let me know if you have any additional clarifying questions and thanks in advance!!
    Last edited by Lemmy Kickit; 12-08-2016 at 03:46 PM. Reason: Descriptive title
    Reply With QuoteRegister To Reply
  2. 12-08-2016, 03:45 PM #2
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: A simple copy paste macro, but with a few twists and turns...

    title updated....
    Last edited by protonLeah; 12-08-2016 at 08:28 PM.
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  3. 12-08-2016, 03:52 PM #3
    YasserKhalil
    YasserKhalil is offline
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Macro to copy value across sheets, using another sheet as index

    May be in sheet1 cell B2 
    Please Login or Register  to view this content.
    Then drag the formula down
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here
    Reply With QuoteRegister To Reply
  4. 12-08-2016, 04:26 PM #4
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro to copy value across sheets, using another sheet as index

    Sorry

    Post deleted.


    Please comply with moderators request
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  5. 12-08-2016, 09:57 PM #5
    Lemmy Kickit
    Lemmy Kickit is offline
    Registered User
    Join Date
    06-12-2016
    Location
    Ann Arbor, MI
    MS-Off Ver
    2010
    Posts
    6

    Re: Macro to copy value across sheets, using another sheet as index

    Quote Originally Posted by mehmetcik View Post
    Sorry

    Post deleted.


    Please comply with moderators request
    I believe I already have, if you take a look at the subject line of the mod's reply compared with the current title of the thread. Sorry for the infraction.

    Quote Originally Posted by YasserKhalil View Post
    May be in sheet1 cell B2 
    Please Login or Register  to view this content.
    Then drag the formula down
    Thank you for this! I already had something similar written out because the size of the actual dataset I'm using is so large that it would be prohibitive to use a formula which was why I was hoping to write a proper macro but this is certainly helpful nonetheless. I would still be happy to field any additional responses but if I don't see anything additional in the next day or so, I will accept this as a solution.
    Reply With QuoteRegister To Reply
  6. 12-08-2016, 11:40 PM #6
    mike7952
    mike7952 is offline
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: Macro to copy value across sheets, using another sheet as index

    Just using Yasser formula in vba

    Please Login or Register  to view this content.
    Last edited by mike7952; 12-09-2016 at 12:17 AM.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    Reply With QuoteRegister To Reply
  7. 12-09-2016, 01:22 PM #7
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro to copy value across sheets, using another sheet as index

    Put this formula in B2 on sheet1 and fill down

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Put this formula in C2 on sheet2 and fill down

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    [/FORMULA]
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Unable to replicate simple macro process/unable to compose simple copy paste macro
    By MattRNR in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-06-2015, 04:51 AM
  2. [SOLVED] What's a simple copy paste macro for this?
    By manzier in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-29-2014, 07:02 PM
  3. Help with simple Copy/Paste macro please!!
    By rodgersmg in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-21-2014, 09:00 AM
  4. [SOLVED] Need help with a simple copy and paste macro
    By margy600 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-14-2014, 11:12 AM
  5. [SOLVED] Simple copy and paste macro- Paste special help needed.
    By hernancrespo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2012, 07:02 AM
  6. [SOLVED] Simple copy and paste macro
    By hernancrespo in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-08-2012, 08:16 AM
  7. Simple copy paste macro
    By Test123Test in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-18-2012, 05:38 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1