Find unique rows on One sheet and Paste to a new sheet - Excel 2010 VBA 1

[JasonEnsor]

Hi Guys,

I am trying to find all the unique values in a column on a worksheet then create a new worksheet in the workbook and past the unique values in. I feel i am part way there however i can't seem to get my data to paste on the newly created sheet. I am sure it is something simple i am missing.

Any help would be appreciated

Sub Get_UniqueData()
   Dim dataSheet As Worksheet
   Dim currentLastRow As Long
   
   Set dataSheet = ActiveSheet
   
   Sheets.Add.name = "UniqueData"
   Sheets("UniqueData").Move AFTER:=Sheets(Sheets.Count)
   Sheets("UniqueData").Select
   
   currentLastRow = dataSheet.Cells(Rows.Count, 1).End(xlUp).row
   Sheets("UniqueData").Range("A1").CurrentRegion.Clear
   
   ActiveSheet.Range("A2:A" & currentLastRow).AdvancedFilter Action:=xlFilterCopy, _
   CopyToRange:=Sheets("UniqueData").Range("A" & currentLastRow), Unique:=True
End Sub

Regards

Jason

 

[PHV]

What happens if you replace this:
ActiveSheet.Range("A2:A" & currentLastRow).AdvancedFilter
with this ?
dataSheet.Range("A2:A" & currentLastRow).AdvancedFilter

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JasonEnsor]

Thanks PHV,

Once again you have fixed my issue. Looking at the code again it makes sense that i wouldn't use ActiveSheet for the Advanced Filter as i have obviously changed the ActiveSheet when i created the new one.

Thanks Again

Jason