+ Reply to Thread
Results 1 to 7 of 7

Copy and paste unique values to another worksheet

  1. #1
    Forum Contributor
    Join Date
    07-06-2007
    Posts
    100

    Copy and paste unique values to another worksheet

    Hi there
    I want to use a combo box in one of the worksheets ("summary") to filter data in another worksheet ("data") within the same workbook. To populate the combo box I want to copy a list of unique values from the column A of the "data" worksheet and paste these values in one of the columns of the "summary" worksheet. I can then use these value as an input range of the format control for my combo box. The combo box control I am using is from Form toolbar available in excel. The data is not static so number of row and the number unique values will change every week.

    Can someone please help me with code for this.

    thanks, gsrai31

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Quote Originally Posted by gsrai31
    Hi there
    I want to use a combo box in one of the worksheets ("summary") to filter data in another worksheet ("data") within the same workbook. To populate the combo box I want to copy a list of unique values from the column A of the "data" worksheet and paste these values in one of the columns of the "summary" worksheet. I can then use these value as an input range of the format control for my combo box. The combo box control I am using is from Form toolbar available in excel. The data is not static so number of row and the number unique values will change every week.

    Can someone please help me with code for this.

    thanks, gsrai31
    Hi,

    You'll need a macro to create a unique list before you can proceed, so I suggest you use a Data Filter Advanced procedure to derive that list. Once you've done that you can then assign that list (suggest giving it a range name),to the RowSource property of the combobox. When you've done that you should find that the list is available for selection in the combobox.

    I'm not quite clear what you're trying to achieve once you've got this far since. Are you wanting to pick one item from the combobox and have the Data sheet filtered on that choice? In which case you need to design a few lines of code that react to say the combobox change event, and which then runs another Data Filter Advanced filter using the value of the combobox for identifying the value in the data sheet that you want to filter for.

    HTH

  3. #3
    Forum Contributor
    Join Date
    07-06-2007
    Posts
    100

    Copy and paste unique values to another worksheet

    Hi HTH
    Thanks for looking into this for me. The second part of the process to use combo box to filter data already work. But the combo box is populated from a static list of values. Can you please write the code that will derive the list using Data Filter Advanced procedure you mentioned and paste the list in another location. List also needs to exclude header text. Thanks for your help.
    gsrai31

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Something like this

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Quote Originally Posted by gsrai31
    Hi HTH
    Thanks for looking into this for me. The second part of the process to use combo box to filter data already work. But the combo box is populated from a static list of values. Can you please write the code that will derive the list using Data Filter Advanced procedure you mentioned and paste the list in another location. List also needs to exclude header text. Thanks for your help.
    gsrai31

    Hi,

    The following assumes

    1. That the top left cell on your data sheet is named 'Data_Top' and that all your data is in a contiguous range (so that the .CurrentRegion property is valid).

    2. The cell on the summary sheet that contains the field heading for which you want a unique list, is named 'Data_Out'

    Now create the following Procedure and run it.

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    07-06-2007
    Posts
    100

    Copy and paste unique values to another worksheet

    Many thanks for your help.
    gsrai31

  7. #7
    Registered User
    Join Date
    07-28-2009
    Location
    Seattle
    MS-Off Ver
    Excel Mac 2011
    Posts
    38

    Re: Copy and paste unique values to another worksheet

    Hello, I have a Q similar to gsrai31's, and possibly the same solution would apply.... if I could understand it the solution you gave.... (I am a marketeer):

    I need to create a summary range (2 columns, approx 8 rows) in a source workbook, then have the values automatically transfer to a similar summary range in the receiving workbook. From there, I will use = to copy the values to their destination cells in the receiving workbook, so the whole thing works "live" to changes in the source values.
    I don't know VB or any macro language .....
    but can follow simple directions.....
    Thanks much for your help,
    phstol

+ 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