Auto Filter Macro

[ammo4814]

HI ,

iam looking for assistance about VB code that can be used for auto filter & copy data from sheet to anathor .

attached sheet explain what iam looking for .

Thanks in advance for your help

 

[Nebu]

Hi:

Use the following code

Sub FilterData()
Application.ScreenUpdating = False
Dim i As Long
Dim j As Long
i = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
j = Sheet2.Cells(Rows.Count, "B").End(xlUp).Row
Sheet1.Range("B7:F" & i).ClearContents
With Sheet2
.Range("B2:F2").AutoFilter Field:=1, Criteria1:=Sheet1.[C3].Value
.Range("B3:F" & j).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet1.[B7]
Application.CutCopyMode = False
.ShowAllData
End With
Application.ScreenUpdating = True
End Sub

Thanks

 

[ammo4814]

Dear ,

Thanks alot for your assistance , i have only one question , when i enter the employee # each time i have to run the macro . is there any way once the employee # entered the data chaneged without need to run the macro each time .

Thanks

 

[Nebu]

Hi :
Add this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C3")) Is Nothing Then
Sheet2.FilterData
End If
End Sub

into Summary sheet Worksheet change event.

Thanks

 

[ammo4814]

Dear Nebu ,

could you please help me how i add this code i try to add it but it not worked .

Thanks

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[ammo4814]

Dears ,

Thanks all for your help , i have one issue some times the data copied will be more or less and there is Grand Total in the below , how i can solve this issue by add code to hide and add rows.

Thanks

 

[Nebu]

Hi:

Is this what you are looking for? The code will dynamically sum the target for the employees as per the filter.

Thanks