Coping Rows based on specific criteria to designated pages.
 

I use the below code to copy rows from multiple workbooks when column 10
equals "Priority". I can no longer have this worksheet as the first
worksheet. I need to adjust the code so that is copies the rows to the master
workbook to the third worksheet in my book. The tab name changes
periodically so I would like it so that it copies to the 3rd worksheet in the
active_workbook.

Thanks Bill

' Copies rows where Category equals Priority
Dim CP_workbook As Workbook
Dim CP_range As Range, CP_range1 As Range
For CP_i = LBound(Saco_Array) To UBound(Saco_Array)
Set CP_workbook = Workbooks(Saco_Array(CP_i))
Set CP_range = CP_datarange(CP_workbook)
Set CP_range1 = Workbooks(ThisWorkbook.Name).Worksheets(1) _
.Cells(Rows.Count, 1).End(xlUp)(2)
Set CP_range2 = Nothing
On Error Resume Next
Set CP_range2 = CP_range.SpecialCells(xlVisible)
On Error GoTo 0
If Not CP_range2 Is Nothing Then
CP_range.Copy Destination:=CP_range1
Else
MsgBox CP_workbook.Name & " has no records matching the Priority"
End If
CP_workbook.Worksheets(1).AutoFilterMode = False
Next


' Function that copies rows where Category equals Priority
Function CP_datarange(CP_bk As Workbook) As Range
Set CP_Sh = CP_bk.Worksheets(1)
CP_Sh.UsedRange.AutoFilter field:=10, Criteria1:=Trim("Priority")
Set CP_range = CP_Sh.AutoFilter.Range
Set CP_range = CP_range.Offset(1).Resize(CP_range.Rows.Count - 1)
Set CP_datarange = CP_range
End Function