Originally Posted by
bmba007
I want it to be sorted by the query value i.e. if I first run with say JIM ACNIO and then RICK BELE, then first the values related to JIM ACNIO will be displayed and then of RICK BELE. I've already posted my desired output in the 1st post of my question. Also I do not want to fetch all column data just specific ones.
Go through my question again for better understanding
Sorry - would you like a refund?
Advanced Data Filter with Criteria gets confused if the column headers are not unique so I had to add "(USD)" and "(EURO)"
Option Explicit
Sub CopyFiltered()
Dim wsData As Worksheet, wsOutput As Worksheet
Dim rData As Range, rCrit As Range, rOutput As Range
Dim vSort As Variant
Set wsData = Worksheets("Sheet1")
Set wsOutput = Worksheets("Sheet2")
Set rData = wsData.Range("B3").CurrentRegion
Set rCrit = wsData.Range("M3").CurrentRegion
Set rOutput = rCrit.Cells(1, rCrit.Columns.Count + 2)
wsOutput.Range("J3").CurrentRegion.EntireColumn.Delete
wsData.Select
rData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=rOutput, Unique:=False
vSort = Application.WorksheetFunction.Transpose(rCrit.Cells(2, 1).Resize(rCrit.Rows.Count - 1, 1).Value)
Application.AddCustomList ListArray:=vSort
wsData.Sort.SortFields.Clear
'sort on custom order with header
rOutput.Cells.Sort Key1:=rOutput.Columns(2), Order1:=xlAscending, _
Orientation:=xlTopToBottom, Header:=xlYes, MatchCase:=False, _
OrderCustom:=Application.CustomListCount + 1
wsData.Sort.SortFields.Clear
Application.DeleteCustomList Application.CustomListCount
With wsOutput
rOutput.CurrentRegion.Cut .Range("I3")
.Columns("I:I").Clear
.Columns("K:K").Cut .Columns("R:R")
.Columns("K:K").Delete Shift:=xlToLeft
.Range("L3").Value = "SALARY (USD)"
.Columns("M:M").Delete Shift:=xlToLeft
.Columns("M:M").Delete Shift:=xlToLeft
.Range("M3").Value = "SALARY P.A. (EURO)"
.Columns("N:N").Delete Shift:=xlToLeft
.Columns("N:N").NumberFormat = "dd mmmm yyyy"
.Range("J3").CurrentRegion.EntireColumn.AutoFit
End With
Application.CutCopyMode = False
End Sub