+ Reply to Thread
Results 1 to 2 of 2

Copy specific rows to another tab depending on content.

Hybrid View

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    New Plymouth Taranaki
    MS-Off Ver
    2013
    Posts
    29

    Copy specific rows to another tab depending on content.

    Hi, I have code to copy rows and paste into another tab and I have code to find rows that have a specific text string and delete it. What I want to do is combine them somehow to copy to another tab only rows that have the text string -Barrett Road in column B

    Sub LoopCopy()
    
        Dim shWO As Worksheet, shAss As Worksheet
        Dim WOLastRow As Long, Iter As Long
        Dim RngToCopy As Range, RngToPaste As Range
    
        With ThisWorkbook
            Set shWO = .Sheets("PL Raw data - Combined") 'Modify as necessary.
            Set shAss = .Sheets("Test") 'Modify as necessary.
        End With
    
        'Get the row index of the last populated row in column A.
        'Change accordingly if you want to use another column as basis.
        'Two versions of getting the last row are provided.
        ' WOLastRow = shWO.Range("A2").End(xlDown).Row
        WOLastRow = shWO.Range("A" & Rows.Count).End(xlUp).Row
    
        For Iter = 2 To WOLastRow
            Set RngToPaste = shAss.Range("A" & (Iter + 2))
            With shWO
                Set RngToCopy = Union(.Range("A" & Iter), .Range("P" & Iter & ":S" & Iter))
                RngToCopy.Copy RngToPaste
            End With
        Next Iter
    
    End Sub
    Sub sbDelete_Rows_IF_Cell_Cntains_String_Text_Value()
    Dim lRow As Long
    Dim iCntr As Long
    lRow = 100
    For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1) = "Barrett Road" Then
    Rows(iCntr).Delete
    End If
    Next
    End Sub
    Thanks

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Copy specific rows to another tab depending on content.

    Sub RunMe()
    Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1") 'source sheet
    Dim ws2 As Worksheet:   Set ws2 = Sheets("Sheet2") 'output sheet
    Dim rCell As Range
    
    For Each rCell In ws1.Range("B1:B" & ws1.Range("B" & Rows.Count).End(xlUp).Row)
        If Not InStr(1, rCell, "- Barrett Road") = 0 Then
            rCell.EntireRow.Copy ws2.Range("B" & Rows.Count).Offset(0, -1).End(3)(2)
        End If
    Next rCell
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to delete rows depending on cell content
    By grim72 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2014, 11:51 AM
  2. Adding Multiple Rows Depending on Column Content
    By ryanb4614 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2014, 09:58 AM
  3. Macros auto hiding of rows depending on cell content
    By nad23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2012, 12:40 PM
  4. [SOLVED] Return a specific column of data depending on the content of a cell
    By Herr Rommel in forum Excel General
    Replies: 2
    Last Post: 05-07-2012, 02:21 PM
  5. Hide rows depending on content...
    By Gord in forum Excel General
    Replies: 1
    Last Post: 05-12-2005, 05:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1