MACRO 2 COPY UNIQUES TO NEW SHEET

[Faraz A. Qureshi]

Could one devise a macro to copy only uniques from a specific column(s) of
the "Selected" sheets to a new sheet? Because copying them all together on a
new sheet and then removing the duplicates won't be possible as the entries
in all might exceed the total rows of a single sheet.

Furthermore, the column of one of sheet might contain the entries already in
other sheet copied to the new sheet, the macro could also exclude such codes.

Thanx in advance 4 all your help.

 

[Jacob Skaria]

Faraz

Why dont you try Advanced Filter option. Suppose you have data in Sheet1;
and you want the unique list in Sheet2.

1. Assign a header to your column.
2. Activate Sheet2 and access the menu Data>Filter>Autofilter and 'Copy to
another location'.
3. In list range type the range Sheet1!A1:A5000
4. In copy to type/select cell A1
5. Check 'Unique records only'
6. Click OK will give you the unique list in Sheet2.

If this post helps click Yes

 

[Faraz A. Qureshi]

Great Idea Jacob!

Thanx!!!

I am really thankful 4 all your help pal! However, can't the same task be
also conducted via a macro?

Thanx again.

 

[Jacob Skaria]

Faraz

I have not replied with a macro coz you have posted your query in General
Questions; and I see you always in this group. No problems at all. Just for
your information. If you post your queries in the right group that might give
you a speedy response..and on top of that; this would help other users
searching for information in these groups..

For programming related
http://www.microsoft.com/office/com...rosoft.public.excel.programming&lang=en&cr=US

For Worksheetfunctions
http://www.microsoft.com/office/com...ublic.excel.worksheet.functions&lang=en&cr=US

Regarding the unique entries macro Certainly you can.. Try the below macro
which compares Sheet1 ColA and Sheet2 ColA. Adjust to suit your requirement.


Sub Macro()
Dim lngRow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngLastRow1 As Long, lngLastrow2 As Long

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

lngLastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lngLastrow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = 1 To lngLastRow1
If WorksheetFunction.CountIf(ws2.Range("A1:A" & _
lngLastrow2), ws1.Range("A" & lngRow)) = 0 Then
lngLastrow2 = lngLastrow2 + 1
ws2.Range("A" & lngLastrow2) = ws1.Range("A" & lngRow)
End If
Next
End Sub


If this post helps click Yes

 

[Faraz A. Qureshi]

Thanx again Jacob,

However, usually I found the replies only in this group. But your guidance
in other other groups too will be helpful 4 sure

 

[Don Guillett]

Makes list and copies to UNused range and then deletes. Modify to suit

Sub MakeUniqueandcopytoothersheet()
Range("A1:B14").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("h1"), Unique:=True
With Range(Range("h1"), Range("h1").End(xlDown))
..Copy Sheets("sheet5").Range("a9")
..Clear
End With
End Sub