Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find and copy rows based on specific criteria? | New Users to Excel | |||
How do I repeat specific rows on some pages and then change for ot | Excel Discussion (Misc queries) | |||
Sum specific column rows based on 2 different column criteria | Excel Worksheet Functions | |||
How do I repeat rows at the top of specific pages (Excel)? | Excel Discussion (Misc queries) | |||
I want to delete certain rows based on specific criteria | Excel Programming |