Locating certain number strings, copy and paste to other sheet

dwrowe001

New Member
Joined
Mar 12, 2017
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHI
1Main list
26/13/2015171119201
36/14/20158152232554
46/15/20152111536604
56/16/2015131944512
66/17/2015592134483
76/18/201517223139491
86/19/20159132347531
96/20/201518293757581
106/21/2015262230444
116/22/201513172945512
126/23/201512539603
136/24/20158173240412
146/25/2015491016502
156/26/20153123940491
166/27/20152152026564
176/28/2015571135513
186/29/20159174148593
196/30/201513253348551
207/1/2015171739573
217/2/2015
227/3/2015
MList


Question example.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
11
2All #1 Draws1 Draw Before1 Draw After2 Draws After
36/13/2015171119201No draw before 6/13/2015 in "Mlist"6/14/201581522325546/15/20152111536604
46/16/20151319445126/15/201521115366046/17/20155921344836/18/201517223139491
56/23/2015125396036/22/2015131729455126/24/201581732404126/25/2015491016502
67/1/20151717395736/30/201513253348551
7
# 1
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Habtest

New Member
Joined
Jul 30, 2020
Messages
48
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
VBA Code:
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

New Member
Joined
Mar 12, 2017
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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

New Member
Joined
Jul 30, 2020
Messages
48
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

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