Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COPY
I would like extract the entire row of information from the received excel
spreadsheet into the non-compliance excel spreadsheet that meets the criteria of "OLD", "0" "XL", "XC", "XR" or "VAC". There are five columns that could contain this information. Please note that it is possible for none or many of these entries to be in each row. I've unsuccessfully tried several things that did not work (lookup, filter and macro). Any suggestions? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COPY
The following macro will do what you want if I understand you correctly.
You say there are 5 columns that could contain the criteria. I assumed those columns to be column numbers 2, 4, 6, 8, 10. I also assumed your data starts in Column A in the first row. Also, the second sheet is named "Second". HTH Otto Sub MoveData() Dim ColA As Range, RowRng As Range, c As Long, k As Range, i As Range Set ColA = Range("A1", Range("A" & Rows.Count).End(xlUp)) For c = ColA.Count To 1 Step -1 Set i = ColA(c) Set RowRng = Range(i, Cells(i.Row, Columns.Count).End(xlToLeft)) For Each k In RowRng With Sheets("Second") If k.Value = "OLD" Or _ k.Value = "0" Or _ k.Value = "XL" Or _ k.Value = "XC" Or _ k.Value = "XR" Or _ k.Value = "VAC" Then RowRng.Copy .Range("A" & Rows.Count).End(xlUp).Offset(1) ColA(c).EntireRow.Delete End If End With Next k Next c End Sub "Extracting Rows Excel to Excel" <Extracting Rows Excel to wrote in message ... I would like extract the entire row of information from the received excel spreadsheet into the non-compliance excel spreadsheet that meets the criteria of "OLD", "0" "XL", "XC", "XR" or "VAC". There are five columns that could contain this information. Please note that it is possible for none or many of these entries to be in each row. I've unsuccessfully tried several things that did not work (lookup, filter and macro). Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy with format | New Users to Excel | |||
Disable Cut & Copy then restore on exiting | Excel Discussion (Misc queries) | |||
Copy tabs(sheets) from workbook without link to original source | Excel Discussion (Misc queries) | |||
Copy shortcut | Excel Worksheet Functions | |||
Copy without Hidden Cols - How | Excel Discussion (Misc queries) |