#1   Report Post  
Old September 26th 06, 08:55 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 1
Default 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   Report Post  
Old September 26th 06, 11:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,090
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy with format horst New Users to Excel 16 September 25th 06 09:43 PM
Disable Cut & Copy then restore on exiting sparx Excel Discussion (Misc queries) 0 April 30th 06 08:43 PM
Copy tabs(sheets) from workbook without link to original source Rich Ulichny Excel Discussion (Misc queries) 3 August 25th 05 02:11 AM
Copy shortcut BJH Excel Worksheet Functions 0 August 10th 05 12:41 AM
Copy without Hidden Cols - How abrogard Excel Discussion (Misc queries) 1 July 15th 05 07:54 AM


All times are GMT +1. The time now is 07:42 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017