+ Reply to Thread
Results 1 to 3 of 3

search for value then copy and paste a range

Hybrid View

  1. #1
    Registered User
    Join Date
    10-04-2010
    Location
    south africa
    MS-Off Ver
    Excel 2003
    Posts
    11

    search for value then copy and paste a range

    Hi
    Say i have the following data on sheet1 and i want a vba that searches where cell G1 = 22 and cell H = 47 then copies cell range A1 to E6(the first match) to sheet 2, leaves a blank line open and then copies cells A13 to E18(second match) to sheet 2 and carries on until no more matches are found

    Thanks
    Jaco

    A B C D E F G H
    22 11 44 40 36 5 22 47
    35 8 30 26 28 2536
    10 34 16 1 13
    17 18 7 2 42
    45 5 33 12 31
    11 33 17 45 18 5 25 46
    2 12 44 36 31 2633
    26 16 13 34 30
    22 5 28 42 1
    35 10 7 8 40
    30 13 18 8 7 5 22 47
    1 35 10 28 36 4337
    42 33 12 16 44
    11 45 5 34 40
    2 22 26 31 17
    Attached Files Attached Files
    Last edited by jacojvv; 08-14-2012 at 05:02 AM.

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: search for value then copy and paste a range

    This should work

    Sub a()
     Const shData As String = "Sheet1"
     Const shDest As String = "Sheet2"
     Dim wsData As Worksheet, wsDest As Worksheet
     Dim rng As Range, Cell As Range
     Dim LastRow As Long
        Set wsData = Worksheets(shData)
        Set wsDest = Worksheets(shDest)
        
        Set rng = wsData.Range("A1", wsData.Cells(Rows.Count, 1).End(xlUp))
     
        For Each Cell In rng
            If wsData.Cells(Cell.Row, "G") = 22 And wsData.Cells(Cell.Row, "H") = 47 Then
                LastRow = wsDest.Cells(Rows.Count, 1).End(xlUp).Row + 2
                wsData.Range(wsData.Cells(Cell.Row, "A"), wsData.Cells(Cell.Row, "E")).Copy _
                  wsDest.Cells(LastRow, 1)
            End If
        Next Cell
        
        Set wsData = Nothing
        Set wsDest = Nothing
        Set rng = Nothing
        Set Cell = Nothing
    End Sub
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: search for value then copy and paste a range

    Try this

    Sub a()
     Const shData As String = "Sheet1"
     Const shDest As String = "Sheet2"
     Dim wsData As Worksheet, wsDest As Worksheet
     Dim rng As Range, Cell As Range
     Dim LastRow As Long
        Set wsData = Worksheets(shData)
        Set wsDest = Worksheets(shDest)
        
        Set rng = wsData.Range("A1", wsData.Cells(Rows.Count, 1).End(xlUp))
        LastRow = 1
        For Each Cell In rng
            If wsData.Cells(Cell.Row, "G") = 22 And wsData.Cells(Cell.Row, "H") = 47 Then
                
                wsData.Range(wsData.Cells(Cell.Row, "A"), wsData.Cells(Cell.End(xlDown).Row, "E")).Copy _
                  wsDest.Cells(LastRow, 1)
                  LastRow = wsDest.Cells(Rows.Count, 1).End(xlUp).Row + 2
            End If
        Next Cell
        
        Set wsData = Nothing
        Set wsDest = Nothing
        Set rng = Nothing
        Set Cell = Nothing
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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