Copy Unique Values to a New Sheet

[lomo0208]

I have a list of names in column A and data (not used for this step) in other columns on the first sheet. I need to copy the names from column A to the second sheet and I only want unique values. I cannot use the advanced filter (unless I am using it wrong) because I will continue to add names to to column A on sheet 1 and want any new names I add to auto populate on the second sheet.

I appreciate your help!

 

[JoeMo]

If you place this sheet code in a module for the sheet that contains your list, it will automatically update the list on the second sheet whenever a change is made on the first sheet. Note that the second sheet is "Sheet2" in the code below. Change that to match the name of your second sheet. The list on the second sheet is assumed to be in col A.

To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Exit Sub
If Not Intersect(Target, Columns("A")) Is Nothing Then
    With Sheets("sheet2")
        .Columns("A").ClearContents
        Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).AdvancedFilter _
            Action:=xlFilterCopy, copytorange:=.Range("A1"), unique:=True
        .Columns("A").AutoFit
    End With
End If
End Sub

 

[lomo0208]

Thank you. I tried it and nothing changes.

I tried adding the code to sheet 1 and sheet 2. Macros are enabled and the sheet is saved as .xlsm.
Sheet 2 does have a different name, Comp Time Summary. I tried changing sheet 2 to Comp Time Summary and CompTimeSummary in the code and I still do not get results.

I am sure I am doing something wrong, just not sure what.

 

[JoeMo]

Thank you. I tried it and nothing changes.

I tried adding the code to sheet 1 and sheet 2. Macros are enabled and the sheet is saved as .xlsm.
Sheet 2 does have a different name, Comp Time Summary. I tried changing sheet 2 to Comp Time Summary and CompTimeSummary in the code and I still do not get results.

I am sure I am doing something wrong, just not sure what.

The code is for sheet1, not both sheets. Change Sheet2 in the code to Comp Time Summary between the quote marks. Then, select a cell in your list in sheet1, press the F2 key and then press enter. This creates a change event that will trigger the code and produce a list of unique names on the sheet Comp Time Summary. Any subsequent changes or additions you make on sheet1 will automatically trigger the code.

 

[lomo0208]

The code is for sheet1, not both sheets. Change Sheet2 in the code to Comp Time Summary between the quote marks. Then, select a cell in your list in sheet1, press the F2 key and then press enter. This creates a change event that will trigger the code and produce a list of unique names on the sheet Comp Time Summary. Any subsequent changes or additions you make on sheet1 will automatically trigger the code.

Perfect! F2 is what I needed.

Thank you!

 

[JoeMo]

Perfect! F2 is what I needed.

Thank you!

You are welcome - thanks for the reply.