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

walkster220

New Member
Joined
Dec 28, 2016
Messages
17
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?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,974
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Dec 28, 2016
Messages
17
iejic5.jpg
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,974
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Dec 28, 2016
Messages
17

ADVERTISEMENT

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,974
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
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

New Member
Joined
Dec 28, 2016
Messages
17

ADVERTISEMENT

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
iejic5.jpg
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,974
Office Version
  1. 2013
Platform
  1. Windows
It is helpful to have all the details included in the original post. See if this does what you want.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,160
Messages
5,594,600
Members
413,917
Latest member
devansh02

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
Top