Moveing rows to another worksheet based on text in a cell

[utahguy9384]

I'm somewhat new to VB in excel but would like to make a macro that will move rows from one worksheet to another worksheet.

I have an excel spreadsheet with multiple columns and rows, within one of the columns there is some text that refers to SOS or County, I need to move everything that has SOS to another worksheet title UCC SOS and everything that has county to be moved to another worksheet title UCC County. I dont even know where to start, sorry i'm new to all this. Here is a sampling of the worksheet
. Thank you for your help

Account</SPAN>

Primary Borrower</SPAN>

Corp</SPAN>

Status</SPAN>

UCC Type</SPAN>

Lien Type</SPAN>

jurisdiction</SPAN>

Filing #</SPAN>

Filing Date</SPAN>

Expiration date</SPAN>

Continuation Date</SPAN>

Assignor Name</SPAN>

Assignor Address</SPAN>

Assignee Name</SPAN>

Assignee Address</SPAN>

Description</SPAN>

Related Filing Nbr</SPAN>

Debtors</SPAN>

0007032-101</SPAN>

C & P, LLC</SPAN>

VIE 2003-1 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

CA Santa Cruz</SPAN>

39221/20010031105</SPAN>

5/25/2001</SPAN>

5/24/2006</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

0007032-101</SPAN>

C & P, LLC</SPAN>

VIE 2003-1 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

CA Santa Cruz County</SPAN>

39222/20010031106</SPAN>

5/25/2001</SPAN>

5/25/2006</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

FF-Fixtures covered in D/T.</SPAN>

C & P, LLC </SPAN>

0009361-002</SPAN>

SDC Real Estate Associates, LLC</SPAN>

VIE 2003-2 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

CO Arapahoe County</SPAN>

B2116568</SPAN>

6/27/2002</SPAN>

6/27/2007</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-2</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

FF-Fixtures covered in D/T.</SPAN>

0009844-001</SPAN>

Aaron Ventures I, LLC</SPAN>

VIE 2003-1 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

SC Laurens County</SPAN>

200300376</SPAN>

1/13/2003</SPAN>

1/12/2013</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

0009845-001</SPAN>

Aaron Ventures I, LLC</SPAN>

VIE 2003-1 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

SC Pickens County</SPAN>

000000675</SPAN>

1/10/2003</SPAN>

1/9/2013</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

0009847-001</SPAN>

Aaron Ventures I, LLC</SPAN>

VIE 2003-1 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

SC Darlington County</SPAN>

200300000006</SPAN>

1/15/2003</SPAN>

1/14/2013</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

0009848-001</SPAN>

Aaron Ventures I, LLC</SPAN>

VIE 2003-1 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

GA-Chatham County (SOS)</SPAN>

0252003000333</SPAN>

1/22/2003</SPAN>

1/22/2013</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

0009848-001</SPAN>

Aaron Ventures I, LLC</SPAN>

VIE 2003-1 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

NM Lea County</SPAN>

31487 Bk1194 Pp508</SPAN>

12/20/2002</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

FF-Fixtures covered in D/T.</SPAN>

0009849-001</SPAN>

Aaron Ventures I, LLC</SPAN>

VIE 2003-1 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

FL Pasco County</SPAN>

2003008700</SPAN>

1/15/2003</SPAN>

1/14/2013</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

0009849-001</SPAN>

Aaron Ventures I, LLC</SPAN>

VIE 2003-1 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

GA SOS-Georgia</SPAN>

0252003000339</SPAN>

1/22/2003</SPAN>

1/21/2013</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

0009851-001</SPAN>

Aaron Ventures I, LLC</SPAN>

VIE 2003-1 MM</SPAN>

Active</SPAN>

Original</SPAN>

Blanket</SPAN>

GA SOS-Georgia</SPAN>

0252003000340</SPAN>

1/22/2003</SPAN>

1/21/2013</SPAN>

GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>

6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>

c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=2><COL span=4><COL><COL><COL></COLGROUP>

 

[Momentman]

This should work except for one row which is funny "GA-Chatham County (SOS)"

Sub newtest()
    Dim wks As Worksheet
    Dim wksnew As Worksheet
    Dim wksnew2 As Worksheet
    lastrow = Range("A1").End(xlDown).Row
    lastSOSrow = 1
    lastCountyrow = 1
    Set wks = ActiveWorkbook.ActiveSheet
    
    For I = 1 To lastrow
        wks.Activate
        If InStr(1, Cells(I, "G"), "SOS") > 1 Then
        Rows(I).EntireRow.Copy
            If lastSOSrow = 1 Then
                Set wksnew = Worksheets.Add
                wksnew.Name = "UCC SOS"
                wksnew.Activate
                Range("A" & lastSOSrow).Select
                ActiveSheet.Paste
                lastSOSrow = lastSOSrow + 1
            Else
                wksnew.Activate
                Range("A" & lastSOSrow).Select
                ActiveSheet.Paste
                lastSOSrow = lastSOSrow + 1
            End If
        ElseIf InStr(1, Cells(I, "G"), "County") > 1 Then
            Rows(I).EntireRow.Copy
            If lastCountyrow = 1 Then
                Set wksnew2 = Worksheets.Add
                wksnew2.Name = "UCC County"
                wksnew2.Activate
                Range("A" & lastCountyrow).Select
                ActiveSheet.Paste
                lastCountyrow = lastCountyrow + 1
            Else
                wksnew2.Activate
                Range("A" & lastCountyrow).Select
                ActiveSheet.Paste
                lastCountyrow = lastCountyrow + 1
            End If
        End If
    Next I
End Sub

 

[utahguy9384]

Thank you Momentman, you are awesome it worked great

 

[Momentman]

Thanks for the feedback, you are welcome

 

[utahguy9384]

Momentman, i'm sorry to bother you, I forgot to mention that is it possible to delete the rows from the orginial sheet once they have been put to the new sheets?

 

[Momentman]

Momentman, i'm sorry to bother you, I forgot to mention that is it possible to delete the rows from the orginial sheet once they have been put to the new sheets?

No bothers at all. To answer your question, It is possible, Rather than copying to a new sheet, I changed the code to do a CUT.

I also modified the code to have the Column headers on the newly created sheets.

Sub newtest()
    Dim wks As Worksheet
    Dim wksnew As Worksheet
    Dim wksnew2 As Worksheet
    lastrow = Range("A1").End(xlDown).Row
    lastSOSrow = 2
    lastCountyrow = 2
    Set wks = ActiveWorkbook.ActiveSheet
    
    For I = 2 To lastrow
        wks.Activate
        If InStr(1, Cells(I, "G"), "SOS") > 1 Then
        Rows(I).EntireRow.Cut
            If lastSOSrow = 2 Then
                Set wksnew = Worksheets.Add
                wksnew.Name = "UCC SOS"
                
                wksnew.Activate
                Range("A" & lastSOSrow).Select
                ActiveSheet.Paste
                
                With wksnew.Range("A1:R1")
                    .Value = wks.Range("A1:R1").Value
                    .Font.Bold = True
                End With
                
                lastSOSrow = lastSOSrow + 1
            Else
                wksnew.Activate
                Range("A" & lastSOSrow).Select
                ActiveSheet.Paste
                lastSOSrow = lastSOSrow + 1
            End If
        ElseIf InStr(1, Cells(I, "G"), "County") > 1 Then
            Rows(I).EntireRow.Cut
            If lastCountyrow = 2 Then
                Set wksnew2 = Worksheets.Add
                wksnew2.Name = "UCC County"
                
                wksnew2.Activate
                Range("A" & lastCountyrow).Select
                ActiveSheet.Paste
                
                With wksnew2.Range("A1:R1")
                    .Value = wks.Range("A1:R1").Value
                    .Font.Bold = True
                End With
                
                
                lastCountyrow = lastCountyrow + 1
            Else
                wksnew2.Activate
                Range("A" & lastCountyrow).Select
                ActiveSheet.Paste
                lastCountyrow = lastCountyrow + 1
            End If
        End If
    Next I
End Sub

 

[utahguy9384]

Momentman, you are amazing, Thank you for your help. It works amazing and just as I was hoping for.
Thank you again.

 

[Momentman]

Momentman, you are amazing, Thank you for your help. It works amazing and just as I was hoping for.
Thank you again.

You are welcome