Can we Build a Paste Special Menu Button to Paste Unique Values

[regel_1999]

I do a lot of filtering for lots of different spreadsheets. One thing I do about 100 times a day is copy-paste unique values from a column using the typical "Data->Sort & Filter->Advanced->Copy Unique Filters to Cell ___"

So that requires a lot of clicking.

I can't create a macro that does it because the spreadsheets change too much - most of what I do is analyze various forms of data that can include costs, manufacturer, location, material, etc. It depends on what my company asks for. I often filter out unique values to create tables that summarize Vendor A had XXXX defects, or there are XXX parts at Location B.

It would be nice if there was a button on the PASTE SPECIAL menu that could paste just unique values, in the same way there's a button to PASTE SPECIAL -> VALUES and PASTE SPECIAL -> FORMULAS.

Anyone have any ideas? I think it would be a macro, which shouldn't be tough to write, and then the button would have to be inserted into the drop menu via the registry, I think.

Thanks in advance!

 

[jjlwork]

I have a simple macro that does something like this, I've assigned it to "ctrl+k" in my personal workbook. Assigning it to a context menu is something I've never tried. It uses the remove duplicates feature of excel to keep it simple and the added advantage of using remove duplicates is that you can copy and paste multiple columns at a time and it will prompt to select the column# that should be unique if there is more than 1. If you are adding it to the context menus, probably wouldn't be difficult to add in the different paste types as well, currently it uses pasteValues only.

Sub PasteUnique()
'
' PasteUnique Macro
'
' Keyboard Shortcut: Ctrl+k


    'Handles nothing in clipboard to paste
    On Error Resume Next
        Selection.PasteSpecial Paste:=xlPasteValues
    
        If Err Then
            MsgBox "Nothing on clipboard": Err.Clear
        End
        End If
    
    'check how many columns there are and find out which one we want to use if more than one
    Dim columnCount As Integer
    Dim uniqueColumn As Integer
    
    columnCount = Selection.Columns.Count
    uniqueColumn = 1
    
    If columnCount > 1 Then
        uniqueColumn = CInt(InputBox("Select column number that should have unique values"))
    End If
    
    'Remove the duplicates
    Selection.RemoveDuplicates Columns:=uniqueColumn
    
End Sub