+ Reply to Thread
Results 1 to 12 of 12

Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

  1. 03-13-2015, 01:34 PM #1
    hamidxa
    hamidxa is offline
    Forum Contributor
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    113

    Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    Hi,

    I have a workbook that contains 2 sheets.
    Sheet1 has two columns that hold Division and Region numbers
    Sheet2 has a cell, E1, that requests for a user to enter a Division number.

    If a user enters a Division number in cell E1 of Sheet2, a macro should be able to go to Sheet1, and copy/pate all of the rows from Sheet1 where the corresponding Division number appears, into Sheet2.

    Sufficed to say, I would definitely appreciate some help on this.

    All I really have to go off of is a macro that simply copies and pastes All of the Divisions and Regions in Sheet1 onto Sheet2, but I need to be able to tell it to look to the value in Cell E1, and then pull in the Divisions and Regions from Sheet1 only if the Division number in Sheet1 matches the Division Number entered into Cell E1 of Sheet2...if that makes sense.

    Hopefully the attached workbook will shed some additional light on this.


    Thank you in advance!

    BTW, I also added another Sheet, WhatSheet2ShouldLookLikewMacro illustrating the desired output if, in that example, a user enter 1 for the Division Number.
    Attached Files Attached Files
    Last edited by hamidxa; 03-13-2015 at 01:36 PM.
    Reply With QuoteRegister To Reply
  2. 03-13-2015, 01:45 PM #2
    JieJenn
    JieJenn is offline
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    Try 
    Please Login or Register  to view this content.
    Learn Data Analysis or Excel youtube.com/jiejenn
    Reply With QuoteRegister To Reply
  3. 03-13-2015, 01:48 PM #3
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    Do you want your destination Sheet (Sheet2) cleared first or do you want it to compile the data?

    Looks like JieJenn beat me too it.
    Reply With QuoteRegister To Reply
  4. 03-13-2015, 01:59 PM #4
    hamidxa
    hamidxa is offline
    Forum Contributor
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    JieLenn,

    This is awesome.

    Thank you so much for providing this solution!


    John,
    If there is an alternative way that you are aware of, and do not mind sharing, I would truly appreciate a second look as well, as I am trying to absorb all of the VBA I can and would love to study a second solution if possible.

    Thank you both.
    Reply With QuoteRegister To Reply
  5. 03-13-2015, 02:05 PM #5
    hamidxa
    hamidxa is offline
    Forum Contributor
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    Also, a followup question.

    What if Sheet1 had more than just 2 columns?
    Presently it has Division and Region, but what if it also had District?

    It seems that all rows are presently copied over to Sheet2 where the matching criteria applies (i.e., Cell E1 of Sheet2).

    However, what if I only wanted to copy Columns A:B from Sheet1 where that match occurs and ignore the rest of the columns (like the made up Col C that I just created on Sheet1, which I do not want to be copied over to Sheet2)?

    Is there a way to do that?
    Reply With QuoteRegister To Reply
  6. 03-13-2015, 02:13 PM #6
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    No not much in the way of an alternative. I was thinking pretty much along the same route as JieJenn.
    Reply With QuoteRegister To Reply
  7. 03-13-2015, 02:15 PM #7
    hamidxa
    hamidxa is offline
    Forum Contributor
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    John,

    Thanks for that feedback.


    Any thoughts on how to modify the code to always copy the first two columns (Div and Reg) from Sheet1 where the match occurs.
    Right now, it seems like it copies all columns over to Sheet2 (tested this by adding a 3rd column in Sheet1).
    Reply With QuoteRegister To Reply
  8. 03-13-2015, 02:16 PM #8
    simarui
    simarui is offline
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    i came up w/ this... jie's is better i.m.o.

    Please Login or Register  to view this content.
    post that in the Sheet2 Object, not a module.
    Last edited by simarui; 03-13-2015 at 02:22 PM. Reason: using .ClearContents instead of .Delete
    Hope I could help - if your post has been solved don't forget to mark it as such.

    Clicking the * below is a great way to say thanks!

    "Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito
    Reply With QuoteRegister To Reply
  9. 03-13-2015, 02:17 PM #9
    simarui
    simarui is offline
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    oo just saw your follow up... mine will accomplish that!!
    Reply With QuoteRegister To Reply
  10. 03-13-2015, 02:19 PM #10
    JieJenn
    JieJenn is offline
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    Maybe change your line from .Range("A1").CurrentRegion.Offset(1, 0).Copy ws(2).Range("A4") to .Range("A2:B10000").Copy ws(2).Range("A4")
    Reply With QuoteRegister To Reply
  11. 03-13-2015, 02:22 PM #11
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    With JieJenn's code:

    Change:

    Please Login or Register  to view this content.
    to this:

    Please Login or Register  to view this content.
    End With
    Reply With QuoteRegister To Reply
  12. 03-13-2015, 04:53 PM #12
    hamidxa
    hamidxa is offline
    Forum Contributor
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell

    Thanks guys.
    These all worked out really well!
    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] Copy and Paste Range of Cells based on # of cell values in Column A
    By bwaite87 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-17-2014, 05:51 PM
  2. [SOLVED] Copy and paste range as values to specific sheet/range
    By lukestkd in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-06-2014, 09:48 PM
  3. [SOLVED] Copy and paste (values) to another sheet based on condition
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-09-2013, 10:25 PM
  4. Copy and paste values from a range of cells to a target cell based on a countdown clock
    By Consty1 in forum Excel General
    Replies: 1
    Last Post: 10-14-2012, 09:25 AM
  5. Finding a named range based on cell value and copy/paste to same sheet?
    By Simon Lloyd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2006, 06:25 PM

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