+ Reply to Thread
Results 1 to 2 of 2

Copy range of rows

Hybrid View

  1. #1
    Registered User
    Join Date
    12-27-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    43

    Copy range of rows

    Is there a way for this to Copy the row it finds and the 5 rows beneath it?

    Sub customcopy()
    Dim strsearch As String, lastline As Integer, tocopy As Integer
    Dim v As Variant, i As Long, j As Long, c As Range
    strsearch = InputBox("Enter WBSE's To Copy Separated by commas")
    v = Split(strsearch, ",")
    lastline = Range("A65536").End(xlUp).Row
    
    For i = 1 To lastline
      Set c = Range("a" & i & ":Z" & i)
      For j = LBound(v) To UBound(v)
        If Application.CountIf(c, "*" & v(j) & "*") > 0 Then
             Rows(i).Copy Destination:=Sheets("Replace").Cells(Rows.Count, "a").End(xlUp)(2)
             Exit For
        End If
      Next j
    Next i
    
    End Sub
    or even for this to find this value then go so many cells to the right? and then downward for a specific value?

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Copy range of rows

    
    Sub customcopy()
    Dim strsearch As String, lastline As Integer, tocopy As Integer
    Dim v As Variant, i As Long, j As Long, c As Range
    strsearch = InputBox("Enter WBSE's To Copy Separated by commas")
    v = Split(strsearch, ",")
    lastline = Range("A65536").End(xlUp).Row
    
    For i = 1 To lastline
      Set c = Range("a" & i & ":Z" & i)
      For j = LBound(v) To UBound(v)
        If Application.CountIf(c, "*" & v(j) & "*") > 0 Then
             Rows(i & ":" & i + 5).Copy Destination:=Sheets("Replace").Cells(Rows.Count, "a").End(xlUp)(2)
             Exit For
        End If
      Next j
    Next i
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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