I have updated the functionality of my earlier post to only copy over unique values, then I added a sort on the values copied over.


Option Compare Text

'Copy a column from one Table to another
Sub copyUnique()

'Delete current values prior to paste if values exist
If Application.WorksheetFunction.CountA(Range("Table2[[Column1]]")) <> 0 _
Then Range("Table2[[Column1]]").Delete

'Advanced filter for unique entries only
Range("Table1[[#All],[Column3]]").AdvancedFilter Action:=xlFilterInPlace, _
Unique:=True

'Select desired column to copy with destination option
Range("Table1[[Column3]]").Copy _
Destination:=Range("Table2[[Column1]]")

'Remove Filter After Copy
Range("Table1[[#Headers],[Column3]]").Select
ActiveSheet.ShowAllData

'Sort a table column A to Z
Worksheets("Sheet2").ListObjects("Column1").Sort.SortFields.Clear

Worksheets("Sheet2").ListObjects("Column1").Sort.SortFields.Add _
Key:=Range("Table2[[Column1]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

With Worksheets("Sheet2").ListObjects("Column1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

I am not certain if this is the best way to do it but the code is snappy and works for my needs.

Be the first to comment
del.icio.us this! Digg this! RSS 2.0 TOP

On Excel 2010, I created this VBA to copy data from a specific column on a specific table to another table on another spreadsheet to editing the values later without disturbing the original source data.

I am posting it here since I had to do multiple searches for the syntax.

    Sub copyColumn()
    'Delete current values prior to paste if values exist
    If Application.WorksheetFunction.CountA(Range("Table2[Column1]")) <> 0 _
    Then Range("Table2[Column1]").Delete

    'Select desired column to copy with destination option
    Range("Table1[[Column3]]").Copy _
    Destination:=Range("Table2[[Column1]]")

If you find this snippet of code useful, leave a comment.

Be the first to comment
del.icio.us this! Digg this! RSS 2.0 TOP

Posting from my Motorola Droid.

By Mark Miron | Filed in geeking

I found this little app in the market yesterday. I don ‘t need it obviuosly, given how often I post. Hmmm… obviuosly there is no spell check in this app, that may be problematic. :)

1 Comment. Join the Conversation
del.icio.us this! Digg this! RSS 2.0 TOP