Filtering and copying macro

[synetic]

Hey guys,
Im writing a macro where I need it to search through a column in a spreadsheet for the word "WIP" and then copy sections of the row that contains the found word and repaste that in another sheet. O ya and then I need to sort the data based once it is repasted... Does this sound possible? Let me know what I need to clarify.

 

[VHS01]

synetic,

Here is an example of what you want to do.
It is placed in a code module (let us know if you need help) where it shows as a macro

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FindCopySort()<br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#007F00">'This loops through a column looking for "WIP"</SPAN><br>    Sheets("Sheet1").Activate  <SPAN style="color:#007F00">' this selects the sheet to look at</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> Range("A65536").End(xlUp).Row  <SPAN style="color:#007F00">' this starts the loop at row 1 and stops at the last row found</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Range("A" & x).Value = "WIP" <SPAN style="color:#00007F">Then</SPAN>  <SPAN style="color:#007F00">'Looking for "WIP" in column A</SPAN><br>            <SPAN style="color:#007F00">'The three below are just examples of one way to copy, by setting a cell on one sheet equal to a cell on another</SPAN><br>            Sheets("Sheet2").Range("A" & Range("A65536").End(xlUp).Row + 1).Value = Range("A" & x).Value<br>            Sheets("Sheet2").Range("B" & Range("A65536").End(xlUp).Row + 1).Value = Range("B" & x).Value<br>            Sheets("Sheet2").Range("C" & Range("A65536").End(xlUp).Row + 1).Value = Range("C" & x).Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> x<br>    <SPAN style="color:#007F00">'This sorts your list alphabetically using column B as the key sort column</SPAN><br>    Columns("A:C").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _<br>        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br>        DataOption1:=xlSortNormal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

Gary

 

[synetic]

Hey Gary,
Thanks for the help... still having some problems running the code...

Im getting a error for the sort code everytime I run it. Im not sure why. When take that portion out I am able to get it to copy code from sheet1 but it only finds and copies the last one of the rows that had "WIP" in the column instead of pasting every row that had "WIP" in it. Im glad to see that it can copy, please let me know if you can see the problem. Thanks again.

 

[VHS01]

synetic,

Hmmm...the code ran fine. I thought about it for a while and cleaned it up.
Try this and let me know:
<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> FindCopySort()<br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#007F00">'This loops through a column looking for "WIP"</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> Sheets("Sheet1").Range("A65536").End(xlUp).Row  <SPAN style="color:#007F00">' this starts the loop at row 1 and stops at the last row found</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Sheets("Sheet1").Range("A" & x).Value = "WIP" <SPAN style="color:#00007F">Then</SPAN>  <SPAN style="color:#007F00">'Looking for "WIP" in column A</SPAN><br>            <SPAN style="color:#007F00">'The three below are just examples of one way to copy, by setting a cell on one sheet equal to a cell on another</SPAN><br>            lastrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1<br>            Sheets("Sheet2").Range("A" & lastrow).Value = Sheets("Sheet1").Range("A" & x).Value<br>            Sheets("Sheet2").Range("B" & lastrow).Value = Sheets("Sheet1").Range("B" & x).Value<br>            Sheets("Sheet2").Range("C" & lastrow).Value = Sheets("Sheet1").Range("C" & x).Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> x<br>    <SPAN style="color:#007F00">'This sorts your list alphabetically using column B as the key sort column</SPAN><br>    Sheets("Sheet2").Select  <SPAN style="color:#007F00">' this selects the sheet to look at</SPAN><br>    Columns("A:C").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _<br>        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br>        DataOption1:=xlSortNormal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

Gary

 

[synetic]

I must be doing something wrong then or Im missing a add on package.... The sorting section of the code always returns :

Run-time error ‘1004’
Application-defined or object defined error

With the current code I am unable to get anything to transfer to sheet 2. Previously I could get the last row that contained "WIP" to paste.

To run the program I am putting WIP in a few spots in column A (on Sheet 1) along with filling the columns next to them (B and C). I then run the FindCopySort macro.... Let me know how Im messing this up if you can. It was very close to working last time.

Sorry its taking me awhile.

 

[synetic]

I got it to work... This is what I ended up coming up with... Note: CURRENT=Sheet1

Sub FindCopySort3()
Dim x As Integer, lastrow As Integer
'This loops through a column looking for "WIP"
For x = 1 To Sheets("CURRENT").Range("A65536").End(xlUp).Row ' this starts the loop at row 1 and stops at the last row found
If Sheets("CURRENT").Range("A" & x).Value = "WIP" Then 'Looking for "WIP" in column A
'The three below are just examples of one way to copy, by setting a cell on one sheet equal to a cell on another
lastrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1
Sheets("Sheet2").Range("A" & x).Value = Sheets("CURRENT").Range("A" & x).Value
Sheets("Sheet2").Range("B" & x).Value = Sheets("CURRENT").Range("B" & x).Value
Sheets("Sheet2").Range("C" & x).Value = Sheets("CURRENT").Range("C" & x).Value
End If
Next x
Sheets("Sheet2").Activate
Columns("A:C").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Thanks for helping me Gary. Your the king.