VBA Copy over to Sheet2

[Stephen_IV]

I have around 50,000 rows of data. What I need to do is if column O contains "Access Acct" then copy over all of the ID numbers that are associated with it. That would be the rows in yellow. Thanks in advance!

Edison2.csv A B C D E F G H I J K L M N O 1 ID Name Other Repationship Fname Lname Street City State Zip Phone Type Phone Email Address Acces 2 1111 AAAA Access Acct 3 1111 AAAA Mother 4 1111 AAAA Mother 5 1111 AAAA Mother 6 1111 AAAA Father 7 1111 AAAA Father 8 1111 AAAA 9 1111 AAAA 10 1111 AAAA Mother 11 1111 AAAA Cousin 12 1111 AAAA Cousin 13 1111 AAAA Sister 14 2222 BBBB 15 2222 BBBB Mother 16 2222 BBBB Mother 17 2222 BBBB Aunt 18 3333 CCCC Mother 19 3333 CCCC Father 20 3333 CCCC Grandmother 21 3333 CCCC Mother 22 3333 CCCC Mother 23 3333 CCCC Grandfather 24 3333 CCCC Grandmother 25 4444 DDDD Mother 26 4444 DDDD Father 27 4444 DDDD Mother 28 5555 EEEE Mother 29 5555 EEEE Mother 30 5555 EEEE Mother 31 5555 EEEE Father 32 7777 FFFF Grandfather Access Acct 33 7777 FFFF Grandfather Sheet3

 

[davesexcel]

Copy it where?

 

[Stephen_IV]

Sorry! Copy over to Sheet 2

 

[davesexcel]

I assume you mean copy all the data with that ID number

 

[davesexcel]

Try this. Haven't tested it with mega rows. Would probably need an array for 1000's of rows.

Sub Do_It()
    Dim ws As Worksheet, sh As Worksheet
    Dim rng As Range, c As Range
    Dim LstRw As Long, fRng As Range

    Set ws = ActiveSheet
    Set sh = Sheets("Sheet2")
    With ws
        Set rng = .Range("O2:O" & .Cells(.Rows.Count, "O").End(xlUp).Row)
        For Each c In rng.Cells
            If c = "Access Acct" Then
                .Range("A1").AutoFilter field:=1, Criteria1:=c.Offset(, -14)
                LstRw = .Cells(Rows.Count, "A").End(xlUp).Row
                Set fRng = .Range("A2:N" & LstRw).SpecialCells(xlCellTypeVisible)
                fRng.Copy sh.Cells(sh.Rows.Count, "A").End(xlUp).Offset(1)
            End If

            If .AutoFilterMode = True Then .AutoFilterMode = False
        Next c
    End With


End Sub

 

[Stephen_IV]

That is the problem, It will only say Access Acct once (usually the first line). If it does then I need all of the data associated with that ID #. For instance, it says Access Acct with 1111 then I need all of the 1111 data copies to Sheet2.

 

[davesexcel]

Looks like you were answer as I replied.

 

[Stephen_IV]

Okay thanks! I tried it and it works. I appreciate it.