Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO 2 COPY UNIQUES TO NEW SHEET
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. -- Best Regards, Faraz |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO 2 COPY UNIQUES TO NEW SHEET
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 DataFilterAutofilter 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 --------------- Jacob Skaria "Faraz A. Qureshi" wrote: 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. -- Best Regards, Faraz |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO 2 COPY UNIQUES TO NEW SHEET
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. -- Best Regards, Faraz "Jacob Skaria" wrote: 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 DataFilterAutofilter 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 --------------- Jacob Skaria "Faraz A. Qureshi" wrote: 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. -- Best Regards, Faraz |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO 2 COPY UNIQUES TO NEW SHEET
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/comm...&lang=en&cr=US For Worksheetfunctions http://www.microsoft.com/office/comm...&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 --------------- Jacob Skaria "Faraz A. Qureshi" wrote: 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. -- Best Regards, Faraz "Jacob Skaria" wrote: 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 DataFilterAutofilter 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 --------------- Jacob Skaria "Faraz A. Qureshi" wrote: 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. -- Best Regards, Faraz |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO 2 COPY UNIQUES TO NEW SHEET
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 -- Best Regards, Faraz "Jacob Skaria" wrote: 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/comm...&lang=en&cr=US For Worksheetfunctions http://www.microsoft.com/office/comm...&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 --------------- Jacob Skaria "Faraz A. Qureshi" wrote: 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. -- Best Regards, Faraz "Jacob Skaria" wrote: 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 DataFilterAutofilter 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 --------------- Jacob Skaria "Faraz A. Qureshi" wrote: 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. -- Best Regards, Faraz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to copy last sheet in workbook | Excel Discussion (Misc queries) | |||
Macro to copy a sheet and name it | Excel Discussion (Misc queries) | |||
Macro to copy data from One sheet to another | Excel Discussion (Misc queries) | |||
macro to copy columns to sheet | Excel Discussion (Misc queries) | |||
Need Macro to copy specific sheet | Excel Worksheet Functions |