How to get event on AutoFilter using macro and show filtered data in new sheet

[rstar]

Hello ,
Actually what i want to do , i have following data With Auto Filtering ,


Name No R
John 1 r1
Alex 3 r3
Max 45 r8
John 4 r4
Mathew 5 r5
Misael 6 r6
Alex 7 r7


I have to create new sheet for each unique Name selected from filtering .i.e. if John and Alex are selected then 2 new sheets should be created one for John and second for Alex , and each of them show own data (Name + No + R). When Next time if master sheet get updated then news data should be appended when i run macro. i'm using following code but its not working 100%.


Sub mycar()
x = 2
Do While Cells(x, 1) <> ""
If Cells(x, 1) = "John" Then
Worksheets("Sheet1").Rows(x).Copy
Worksheets("Sheet2").Activate
eRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(eRow)
End If
Worksheets("Sheet1").Activate
x = x + 1
Loop
End Sub




-> Here it copy only single data Written in the quotes.
-> Second time if i run this code , it is appending same data again with new data.


Help me to avoid this mistakes.

 

[dermie_72]

See if this works:

Sub rstar()
Dim LR, LR1
Sheets("Sheet1").Range("A2").EntireRow.Insert
LR = Sheets("Sheet1").UsedRange.Rows.Count
LR1 = Sheets("Sheet2").UsedRange.Rows.Count
    With Range("I2:I" & LR)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="John"
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & LR1 + 1)
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
Sheets("Sheet2").Range("A" & LR1 + 1).EntireRow.Delete
End Sub

 

[rstar]

Hi , thanks for your help , now what is happening here it remove "John" record from Sheet1 and paste in Sheet2

 

[dermie_72]

remove ".SpecialCells(xlCellTypeVisible).EntireRow.Delete" from the code, and it will just copy the data.