Filtering and copying macro

synetic

New Member
Joined
Apr 8, 2009
Messages
11
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

VHS01

Board Regular
Joined
Nov 3, 2005
Messages
139
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

New Member
Joined
Apr 8, 2009
Messages
11
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

Board Regular
Joined
Nov 3, 2005
Messages
139
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

New Member
Joined
Apr 8, 2009
Messages
11
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

New Member
Joined
Apr 8, 2009
Messages
11
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,743
Messages
5,626,620
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top