Moveing rows to another worksheet based on text in a cell

U

utahguy9384

New Member
Joined
Nov 30, 2012
Messages
11
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
[TABLE="width: 3039"]
<TBODY>[TR]
[TD]Account</SPAN>[/TD]
[TD]Primary Borrower</SPAN>[/TD]
[TD]Corp</SPAN>[/TD]
[TD]Status</SPAN>[/TD]
[TD]UCC Type</SPAN>[/TD]
[TD]Lien Type</SPAN>[/TD]
[TD]jurisdiction</SPAN>[/TD]
[TD]Filing #</SPAN>[/TD]
[TD]Filing Date</SPAN>[/TD]
[TD]Expiration date</SPAN>[/TD]
[TD]Continuation Date</SPAN>[/TD]
[TD]Assignor Name</SPAN>[/TD]
[TD]Assignor Address</SPAN>[/TD]
[TD]Assignee Name</SPAN>[/TD]
[TD]Assignee Address</SPAN>[/TD]
[TD]Description</SPAN>[/TD]
[TD]Related Filing Nbr</SPAN>[/TD]
[TD]Debtors</SPAN>[/TD]
[/TR]
[TR]
[TD]0007032-101</SPAN>[/TD]
[TD]C & P, LLC</SPAN>[/TD]
[TD]VIE 2003-1 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]CA Santa Cruz</SPAN>[/TD]
[TD]39221/20010031105</SPAN>[/TD]
[TD]5/25/2001</SPAN>[/TD]
[TD]5/24/2006</SPAN>[/TD]
[TD] [/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0007032-101</SPAN>[/TD]
[TD]C & P, LLC</SPAN>[/TD]
[TD]VIE 2003-1 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]CA Santa Cruz County</SPAN>[/TD]
[TD]39222/20010031106</SPAN>[/TD]
[TD]5/25/2001</SPAN>[/TD]
[TD] [/TD]
[TD]5/25/2006</SPAN>[/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]FF-Fixtures covered in D/T.</SPAN>[/TD]
[TD] [/TD]
[TD]C & P, LLC </SPAN>[/TD]
[/TR]
[TR]
[TD]0009361-002</SPAN>[/TD]
[TD]SDC Real Estate Associates, LLC</SPAN>[/TD]
[TD]VIE 2003-2 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]CO Arapahoe County</SPAN>[/TD]
[TD]B2116568</SPAN>[/TD]
[TD]6/27/2002</SPAN>[/TD]
[TD] [/TD]
[TD]6/27/2007</SPAN>[/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-2</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]FF-Fixtures covered in D/T.</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0009844-001</SPAN>[/TD]
[TD]Aaron Ventures I, LLC</SPAN>[/TD]
[TD]VIE 2003-1 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]SC Laurens County</SPAN>[/TD]
[TD]200300376</SPAN>[/TD]
[TD]1/13/2003</SPAN>[/TD]
[TD]1/12/2013</SPAN>[/TD]
[TD] [/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0009845-001</SPAN>[/TD]
[TD]Aaron Ventures I, LLC</SPAN>[/TD]
[TD]VIE 2003-1 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]SC Pickens County</SPAN>[/TD]
[TD]000000675</SPAN>[/TD]
[TD]1/10/2003</SPAN>[/TD]
[TD]1/9/2013</SPAN>[/TD]
[TD] [/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0009847-001</SPAN>[/TD]
[TD]Aaron Ventures I, LLC</SPAN>[/TD]
[TD]VIE 2003-1 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]SC Darlington County</SPAN>[/TD]
[TD]200300000006</SPAN>[/TD]
[TD]1/15/2003</SPAN>[/TD]
[TD]1/14/2013</SPAN>[/TD]
[TD] [/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0009848-001</SPAN>[/TD]
[TD]Aaron Ventures I, LLC</SPAN>[/TD]
[TD]VIE 2003-1 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]GA-Chatham County (SOS)</SPAN>[/TD]
[TD]0252003000333</SPAN>[/TD]
[TD]1/22/2003</SPAN>[/TD]
[TD]1/22/2013</SPAN>[/TD]
[TD] [/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0009848-001</SPAN>[/TD]
[TD]Aaron Ventures I, LLC</SPAN>[/TD]
[TD]VIE 2003-1 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]NM Lea County</SPAN>[/TD]
[TD]31487 Bk1194 Pp508</SPAN>[/TD]
[TD]12/20/2002</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]FF-Fixtures covered in D/T.</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0009849-001</SPAN>[/TD]
[TD]Aaron Ventures I, LLC</SPAN>[/TD]
[TD]VIE 2003-1 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]FL Pasco County</SPAN>[/TD]
[TD]2003008700</SPAN>[/TD]
[TD]1/15/2003</SPAN>[/TD]
[TD]1/14/2013</SPAN>[/TD]
[TD] [/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0009849-001</SPAN>[/TD]
[TD]Aaron Ventures I, LLC</SPAN>[/TD]
[TD]VIE 2003-1 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]GA SOS-Georgia</SPAN>[/TD]
[TD]0252003000339</SPAN>[/TD]
[TD]1/22/2003</SPAN>[/TD]
[TD]1/21/2013</SPAN>[/TD]
[TD] [/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0009851-001</SPAN>[/TD]
[TD]Aaron Ventures I, LLC</SPAN>[/TD]
[TD]VIE 2003-1 MM</SPAN>[/TD]
[TD]Active</SPAN>[/TD]
[TD]Original</SPAN>[/TD]
[TD]Blanket</SPAN>[/TD]
[TD]GA SOS-Georgia</SPAN>[/TD]
[TD]0252003000340</SPAN>[/TD]
[TD]1/22/2003</SPAN>[/TD]
[TD]1/21/2013</SPAN>[/TD]
[TD] [/TD]
[TD]GE Commercial Finance Business Property Corporation, f.k.a. General Electric Capital Business Asset Funding Corporation</SPAN>[/TD]
[TD]6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD]Wells Fargo Bank, N.A., as Trustee for the registered holders of GE Business Loan Pass-Through Certificates, Series 2003-1</SPAN>[/TD]
[TD]c/o GE Commercial Finance Business Property Corporation 6464 185th Avenue NE, Redmond WA 98052-5048</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=2><COL span=4><COL><COL><COL></COLGROUP>[/TABLE]
 
Sort by date Sort by votes
This should work except for one row which is funny "GA-Chatham County (SOS)"

Code: 
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
 
Upvote 0
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?
 
Upvote 0
utahguy9384 said:
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?
Click to expand...

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.

Code: 
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
 
Upvote 0
Momentman, you are amazing, Thank you for your help. It works amazing and just as I was hoping for.
Thank you again.
 
Upvote 0
You must log in or register to reply here.

Share this page

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
Back
Top