Hi,
thanks for your time,
i have tried this macro by getting some error can u please help on same file enclosed for reference.
Option Explicit
Sub copyrows()
Dim lRow As Long
Dim lCol As Integer
Application.ScreenUpdating = False
Sheets("Master Base").Select
lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lCol = sheet1.Cells(1, sheet1.Columns.Count).End(xlToLeft).Column
lCol = lCol + 1
Range("A1:O" & lRow).SpecialCells(xlCellTypeVisible).Copy
Sheets("Calling").Select
Sheets("Calling").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Cells(1, lCol).Value = "Mapping"
Cells(2, lCol).FormulaR1C1 = "=IF(OR(RC[-13]=""AA"",RC[-13]=""AAA"",RC[-12]=""AA"",RC[-12]=""AAA""),1,2)"
Cells(lRow, lCol).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Range("A1

" & lRow).Value = Range("A1

" & lRow).Value
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Calling").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Calling").AutoFilter.Sort.SortFields.Add Key:= _
Range("P1

" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Calling").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("A1

" & lRow").AutoFilter Field:=16, Criteria1:="2"
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.entirerow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Rows(ActiveCell.Row & ":" & lRow).Delete
Selection.AutoFilter
Range("A1").Select
lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Cells(2, lCol).Select
Cells(2, lCol).FormulaR1C1 = "=VLOOKUP(RC[-14],ALLOC!C1,1,0)"
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("A1

" & lRow).Value = Range("A1

" & lRow).Value
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Calling").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Calling").AutoFilter.Sort.SortFields.Add Key:= _
Range("P1

" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Calling").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("A1

" & lRow").AutoFilter Field:=16, Criteria1:="#N/A"
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.entirerow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Rows(ActiveCell.Row & ":" & lRow).Delete
Selection.AutoFilter
Range("A1").Select
Columns(lCol).Delete
End Sub