Locating certain number strings, copy and paste to other sheet

[dwrowe001]

Hi everyone,
I am in need of help.

On the first sheet named “MList”, I have a large list of past draws starting on 6/13/2015. This example list shows only 19 draws. This list is growing as I manually enter draws as they occur to the bottom of the list. There are over a thousand draws in the list now.

What I would like to do is search the Main List on the MList sheet from top down looking for all draws which start with the number 1, copy the Date and all 6 numbers of each instance found and paste each over into a separate sheet named “# 1” starting in row A3 to G3 going down. Then continue searching for each draw starting with#1 until the bottom is reached. So, for example, in the Main List on the MList sheet there are 4 draws which start with #1. I would need a formula or macro that searches the entire Main List until it finds all draws starting with the number 1 and then copy each to the sheet #1. There will be more draws that start with the number 1 in the larger list I have… See the example I have attached for clarification. The list is continually getting longer as I enter draws daily, and as I enter draws daily the macro should act upon those as well.

Next, I need the macro to start at the top of the Main List, going down to locate the very next draw right before each instance of a draw starting with #1.. So, the first draw on 6/13/2015 is a #1 draw, but since it’s the first one in the Main List there isn’t a draw before it. But the draws on 6/16, 6/23, and 7/1 each have a draw before them, one on 6/15, one on 6/22 and one on 6/30. These are the draws I need listed on sheet #1 “1 Draw Before” list, starting on row J3 to P3 down.

Next I need the macro to start at the top of the Main List, going down to locate the draws right after each draw starting with #1. These are draws on 6/14, 6/17, and 6/24. These draws are copied to the #1 sheet, “1 Draw After” list, starting in S3 to Y4 down. Note, there isn’t a draw listed after the last draw on 7/1 yet.. but once I enter one in the Mail List, it would be copied here.

The next group will be those that are the second draw after each draw starting with #1. The macro should copy each of those and copy to the #1 sheet. So, these would be the draws on 6/15, 6/18 and 6/25. Note that there are no draws listed under the last draw of 7/1 yet. Once I enter those into the Main List, then they would be copied here.

Lastly, the macro should allow me to enter draws at the bottom the Main List as they occur daily and then act upon that draw as, putting it in the proper sheet. Note, eventually I will have 60 sheets, one sheet for each number 1 to 60. Each sheet doing the same as sheet #1. I am hoping that I will be able to copy and modify the macro for sheet #1.

Maybe a button on the MList sheet to start the macro after a new draw is entered at the bottom of the Main List?

The Mini Sheets below pretty much shows you what I need the macro to do... I hope all this makes sense.

Thank you in advance for your time and efforts in helping me with this.

I have posted this question about a week ago over on Chandoo. Search for a number, then copy to different sheet

Dave

Question example.xlsx
	A	B	C	D	E	F	G	H	I
1		Main list
2	6/13/2015	1	7	11	19	20	1
3	6/14/2015	8	15	22	32	55	4
4	6/15/2015	2	11	15	36	60	4
5	6/16/2015	1	3	19	44	51	2
6	6/17/2015	5	9	21	34	48	3
7	6/18/2015	17	22	31	39	49	1
8	6/19/2015	9	13	23	47	53	1
9	6/20/2015	18	29	37	57	58	1
10	6/21/2015	2	6	22	30	44	4
11	6/22/2015	13	17	29	45	51	2
12	6/23/2015	1	2	5	39	60	3
13	6/24/2015	8	17	32	40	41	2
14	6/25/2015	4	9	10	16	50	2
15	6/26/2015	3	12	39	40	49	1
16	6/27/2015	2	15	20	26	56	4
17	6/28/2015	5	7	11	35	51	3
18	6/29/2015	9	17	41	48	59	3
19	6/30/2015	13	25	33	48	55	1
20	7/1/2015	1	7	17	39	57	3
21	7/2/2015
22	7/3/2015
MList

Question example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	1
2		All #1 Draws									1 Draw Before									1 Draw After									2 Draws After
3	6/13/2015	1	7	11	19	20	1			No draw before 6/13/2015 in "Mlist"									6/14/2015	8	15	22	32	55	4			6/15/2015	2	11	15	36	60	4
4	6/16/2015	1	3	19	44	51	2			6/15/2015	2	11	15	36	60	4			6/17/2015	5	9	21	34	48	3			6/18/2015	17	22	31	39	49	1
5	6/23/2015	1	2	5	39	60	3			6/22/2015	13	17	29	45	51	2			6/24/2015	8	17	32	40	41	2			6/25/2015	4	9	10	16	50	2
6	7/1/2015	1	7	17	39	57	3			6/30/2015	13	25	33	48	55	1
7
# 1

 

[Habtest]

Sub Macro1()

Dim m, a, i As Integer

m = Worksheets("MList").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count
a = 3

Cells(1, 1).Select

For i = 2 To m
    If Worksheets("MList").Cells(i, 2) = 1 Then
        
        Application.Goto Sheets("MList").Cells(i, 1)
        Range(Cells(i, 1), Cells(i, 7)).Select
        Selection.Copy
        Application.Goto Sheets("#1").Cells(a, 1)
        ActiveSheet.Paste
        
        If i = 2 Then
            Cells(3, 10) = "No draw before 6/13/2015 in MList"
            With Range("J3:P3")
                    .Font.ColorIndex = 3
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .Merge
End With
        Else
            Application.Goto Sheets("MList").Cells(i - 1, 1)
            Range(Cells(i - 1, 1), Cells(i - 1, 7)).Select
            Selection.Copy
            Application.Goto Sheets("#1").Cells(a, 10)
            ActiveSheet.Paste
        End If

        Application.Goto Sheets("MList").Cells(i + 1, 1)
        Range(Cells(i + 1, 1), Cells(i + 1, 7)).Select
        Selection.Copy
        Application.Goto Sheets("#1").Cells(a, 19)
        ActiveSheet.Paste
        
        Application.Goto Sheets("MList").Cells(i + 2, 1)
        Range(Cells(i + 2, 1), Cells(i + 2, 7)).Select
        Selection.Copy
        Application.Goto Sheets("#1").Cells(a, 28)
        ActiveSheet.Paste
                
        a = a + 1
        
    End If
Next i

Application.CutCopyMode = False
Cells(1, 1).Select

End Sub

 

[dwrowe001]

Sub Macro1()

Dim m, a, i As Integer

m = Worksheets("MList").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count
a = 3

Cells(1, 1).Select

For i = 2 To m
    If Worksheets("MList").Cells(i, 2) = 1 Then
       
        Application.Goto Sheets("MList").Cells(i, 1)
        Range(Cells(i, 1), Cells(i, 7)).Select
        Selection.Copy
        Application.Goto Sheets("#1").Cells(a, 1)
        ActiveSheet.Paste
       
        If i = 2 Then
            Cells(3, 10) = "No draw before 6/13/2015 in MList"
            With Range("J3:P3")
                    .Font.ColorIndex = 3
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .Merge
End With
        Else
            Application.Goto Sheets("MList").Cells(i - 1, 1)
            Range(Cells(i - 1, 1), Cells(i - 1, 7)).Select
            Selection.Copy
            Application.Goto Sheets("#1").Cells(a, 10)
            ActiveSheet.Paste
        End If

        Application.Goto Sheets("MList").Cells(i + 1, 1)
        Range(Cells(i + 1, 1), Cells(i + 1, 7)).Select
        Selection.Copy
        Application.Goto Sheets("#1").Cells(a, 19)
        ActiveSheet.Paste
       
        Application.Goto Sheets("MList").Cells(i + 2, 1)
        Range(Cells(i + 2, 1), Cells(i + 2, 7)).Select
        Selection.Copy
        Application.Goto Sheets("#1").Cells(a, 28)
        ActiveSheet.Paste
               
        a = a + 1
       
    End If
Next i

Application.CutCopyMode = False
Cells(1, 1).Select

End Sub

Hi Habtest,
Thank you for creating that macro for me. I'm not sure where to put it though. So I put it into the MList sheet and assigned it to a button. Then clicked the button. An Error box appeared saying "Script out of range" and it also selected the first draw in A2 thru G3. Can you tell what I should do to make it work? Am I putting it in the correct location?

Thank you again for your work.

 

[Habtest]

Hi dwrowe001, you may paste the codes to workbook module:
Alt F11 - (right click) VBA project (...) - Insert - Module - (paste codes)

Then the macro should work, including when assigned to a button.