Sub to carve out uniques based on col header whose position may va

[Max]

In an active sheet, I have col headers in row1, data from row2 down.
I need to carve out uniques based on the "Order ID" col header, and
paste these unique lines into a new sheet, then name this new sheet as:
UniqueOrderIDs

How could the above functionality be done by a sub? There will be only one
col header: Order ID within row1. But its position may vary from day-to-day,
hence the sub needs to locate the col via the header text, then do the
necessary

Manually, I extract like this
Assuming the col header: Order ID is in R1
Using a empty col to the right, in say, AF2:
=IF(R2="","",IF(COUNTIF($R$2:R2,R2)>1,"","x"))
AR2 is then copied down to last row of data in col B (Col B is always used
to determine data extent here). Then I'd apply autofilter on col AF for "x",
copy the entire sheet (with the filtered results) n paste it into a new
sheet, then rename the sheet as: UniqueOrderIDs

 

[Jacob Skaria]

Hi Max

Try the below macro and feedback.

Sub Macro()
'For Max 07 Oct 2009
Dim ws1 As Worksheet, ws2 As Worksheet, lngCol As Long

Set ws1 = ActiveSheet
lngCol = ws1.Rows(1).Find("Order ID").Column
Set ws2 = Worksheets.Add(After:=ActiveSheet)
ws1.Columns(lngCol).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws2.Range("A1"), Unique:=True
ws2.Name = "UniqueOrderIDs"
End Sub

If this post helps click Yes

 

[Max]

Thanks Jacob. But I need the entire sheet of "filtered results" as the output
pasted in UniqueOrderIDs not just the col itself.

 

[Patrick Molloy]

a non-vba method is simplyto data/ advanced filter
have you tried that?


1) copy the column header to the sheet where you want to put the unique
results
2) staying on that sheet run the Advanced filter, using the main sheet to
select the source data.
Advanced filters don't allow data to be extracted to another sheet, but you
can extract TO another sheet this way.

 

[Max]

Thanks for the response. In this instance its needed to be a "hands-free"
process done by a sub, which can then be called from another sub as part &
parcel of the entire process.

 

[Jacob Skaria]

OK...So I have misunderstood.. Try the below

Sub Macro()
'For Max 07 Oct 2009
Dim ws1 As Worksheet, ws2 As Worksheet, lngCol As Long
Dim lngLastrow As Long, lngLastCol As Long

Set ws1 = ActiveSheet
lngCol = ws1.Rows(1).Find("Order ID").Column
lngLastrow = ws1.Cells(Rows.Count, lngCol).End(xlUp).Row
lngLastCol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
Set ws2 = Worksheets.Add(After:=ActiveSheet)
ws1.Columns(lngCol).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ws1.Range(ws1.Range("A1"), ws1.Cells(lngLastrow, _
lngLastCol)).Copy ws2.Range("A1")
ws2.Name = "UniqueOrderIDs"
ws1.AutoFilterMode = False: ws1.ShowAllData
End Sub

If this post helps click Yes

 

[Max]

That worked marvellous. Many thanks, Jacob.