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

rstar

New Member
Joined
Apr 3, 2013
Messages
2
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.
 
Last edited:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
See if this works:

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

New Member
Joined
Apr 3, 2013
Messages
2
Hi , thanks for your help , now what is happening here it remove "John" record from Sheet1 and paste in Sheet2
 

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
remove ".SpecialCells(xlCellTypeVisible).EntireRow.Delete" from the code, and it will just copy the data.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,803
Messages
5,626,997
Members
416,214
Latest member
boston814

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