Excellent, Thanks Lee. That's a great explanation. Yes agree, I really do like it's filtering, familiar with pivot tables but was not with the advanced filtering, very handy. I see what you mean for filtering out duplicates, so for false I would have pasted all the double ups?
As I said earlier I was going to use a vlookup to capture other data in the rows alongside the names however the advanced filtering may be more efficient, can I switch between columns in this? so say I want the data in column 'B' pasted into destination cell 'G' exactly the same way can I add a few lines as below?
Sub ImportValues()
Dim SourceRange As Range
Dim DestinationCell As Range
Dim filename As String
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Sheet1")
Set DestinationCell = .Range("B2")
filename = .Range("U10").Value
If Right(filename, 1) <> "\" Then
filename = filename & "\" & .Range("U11")
Else
filename = filename & .Range("U11")
End If
End With
Workbooks.Open filename
'names
Set SourceRange = ActiveSheet.Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row - 1)
SourceRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=DestinationCell, Unique:=True
'Y Values
Set SourceRange = ActiveSheet.Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row - 1)
Set DestinationCell = ("G2")
SourceRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=DestinationCell, Unique:=True
ActiveWorkbook.Close SaveChanges:=False
DestinationCell.Clear
DestinationCell.EntireColumn.SpecialCells(xlBlanks).Delete Shift:=xlUp
Application.ScreenUpdating = False
Call Imput_formula
End Sub
Bookmarks