Copy and paste unique values to another worksheet

**gsrai31** · 02-28-2008, 06:44 PM

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

**Richard Buttrey** · 02-28-2008, 09:14 PM

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

**gsrai31** · 02-29-2008, 06:06 AM

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

**royUK** · 02-29-2008, 06:13 AM

Something like this

   with Sheet1
.Columns(256).Clear
        .Range(Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, 256), Unique:=True
End With

**Richard Buttrey** · 02-29-2008, 03:05 PM

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.

Sub FilterData()
    Range("Data_Top").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("Data_Out"), Unique:=True

End Sub

**gsrai31** · 03-03-2008, 10:50 AM

Many thanks for your help.
gsrai31

**phstol** · 07-28-2009, 11:44 AM

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