Macro to copy range to new sheet everytime when the word "match" is found

[walkster220]

hi guys need a little help, i have a column (Column D) with a long list of rows containing the word "Match".


i need all instances of "match" with a specific range copied to new worksheet


example "Match" column d row 2 (d:2) I need E2:G9 copied to a new sheet along with every row that contain "Match" in the d column can anyone help?

 

[JLGWhiz]

hi guys need a little help, i have a column (Column D) with a long list of rows containing the word "Match".


i need all instances of "match" with a specific range copied to new worksheet


example "Match" column d row 2 (d:2) I need E2:G9 copied to a new sheet along with every row that contain "Match" in the d column can anyone help?

Why do you need the other 6 rows copied? Will you need blocks of 7 rows for column E:G copied for each match? Please clarify your requirements. Take a little more time and space so you can be clear about what you expect as a result. Also, do you want each block on a different new sheet, or all matches to a single new sheet?

 

[walkster220]

 

[JLGWhiz]

Post #3 does not answer questions. We need to understand the logic that is used to determine what will be copied when a match is found in column D. Your illstration does not answer.

 

[walkster220]

the match is the actual word "match" in column d . so when "match" is located in column D i need E,F,G of the same row with a range of 8 rows down so a total of 24 squares ... example the word "Match" is located on D2 i need E2:G8 copied to a new worksheet and this would apply to every other "Match" located in column D

 

[JLGWhiz]

the match is the actual word "match" in column d . so when "match" is located in column D i need E,F,G of the same row with a range of 8 rows down so a total of 24 squares ... example the word "Match" is located on D2 i need E2:G8 copied to a new worksheet and this would apply to every other "Match" located in column D

That is what I needed to know.

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
    For Each c In sh1.Range("D2", sh1.Cells(Rows.Count, 4).End(xlUp))
        If LCase(c) = "match" Then
            c.Offset(, 1).Resize(8, 3).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End Sub

 

[walkster220]

the macro did something but not what i had in mind .. wherever "match" is located in column i need the 24 rows of E,F,G copied to a new sheet in my work book, these are patterns that i need to log so i need them separated by 1 or 2 rows in the new worksheet like this so i can read them like this


if match is found i need this copied to new worksheet

 

[walkster220]

 

[JLGWhiz]

It is helpful to have all the details included in the original post. See if this does what you want.

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
    For Each c In sh1.Range("D2", sh1.Cells(Rows.Count, 4).End(xlUp))
        If LCase(c) = "match" Then
            If Application.CountA(sh2.Range("A2").Resize(1, 3)) = 0 Then
                c.Offset(, 1).Resize(8, 3).Copy sh2.Range("A2")
            Else
                c.Offset(, 1).Resize(8, 3).Copy sh2.Cells(2, Columns.Count).End(xlToLeft).Offset(, 3)
            End If
        End If
    Next
End Sub