Filter sort in using VBA

[tojomojo]

I've got a simple spread sheet where I am using a pull down box list to select a staff member and then all their sorted tasks should appear on another sheet. It just doesn't populate the target sheet (sheet 4).....

VBA attached to sheet with all tasks and pull down is;

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
On Error Resume Next
If Not Intersect(Range("E2"), Target) Is Nothing Then
Application.EnableEvents = False
If Range("E2").Value = "" Then
Worksheets("Sheet4").ShowAllData
Else
Worksheets("Sheet4").Range("E2").AutoFilter 5, Range("E2").Value
End If
Application.EnableEvents = True
End If
End Sub

 

[tojomojo]

Sorry

I renamed that....'My List' sheet.

Yes copying header is good

when it runs again it can either add to or probably clear and create a whole new list is better.

Thanks

 

[MARK858]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)

    With Application
        .ScreenUpdating = False
        .EnableEvents = True
    End With

    With Sheets("Tasks")

        If Not Intersect(.Range("E2"), Target) Is Nothing Then
            Sheets("My List").Cells.Delete

            If Target.Value <> "" Then
                With .Range("E3:E" & .Range("E" & Rows.Count).End(xlUp).Row)
                    .AutoFilter 1, Target.Value
                    On Error Resume Next
                    .SpecialCells(12).EntireRow.Copy Sheets("My List").Range("A1")
                    On Error GoTo 0
                    Sheets("My List").Columns.AutoFit
                    .AutoFilter
                End With
            End If
        End If

    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

 

[MARK858]

Private Sub Worksheet_Change(ByVal Target As Range)

    With Application
        .ScreenUpdating = False
        .EnableEvents = [COLOR="#FF0000"]False[/COLOR]
    End With

    With Sheets("Tasks")

        If Not Intersect(.Range("E2"), Target) Is Nothing Then
            Sheets("My List").Cells.Delete

            If Target.Value <> "" Then
                With .Range("E3:E" & .Range("E" & Rows.Count).End(xlUp).Row)
                    .AutoFilter 1, Target.Value
                    On Error Resume Next
                    .SpecialCells(12).EntireRow.Copy Sheets("My List").Range("A1")
                    On Error GoTo 0
                    Sheets("My List").Columns.AutoFit
                    .AutoFilter
                End With
            End If
        End If

    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

 

[tojomojo]

Works great thanks....

One last question....using your code segment

If Target.Value <> "" Then                With .Range("E3:E" & .Range("E" & Rows.Count).End(xlUp).Row)
                    .AutoFilter 1, Target.Value
                    On Error Resume Next
                    .SpecialCells(12).EntireRow.Copy Sheets("My List").Range("A1")
                    On Error GoTo 0
                    Sheets("My List").Columns.AutoFit
                    .AutoFilter

If I want a new 'My List' style of sheet to be populated for each staff member as E2 is selected, I think I can modify the first IF line to be (for the instance of the name SD);

If Target.Value = "SD" Then

The call the new List sheet for SD ('SD List' ) would then be...

  .SpecialCells(12).EntireRow.Copy Sheets("SD List").Range("A1")                    On Error GoTo 0
                    Sheets("SD List").Columns.AutoFit
                    .AutoFilter

I can then duplicate this IF for each initial in the pulldown?

Am I right?

Finally on each of the 'lists' how can I make it auto sort by due date?

Really appreciate the help

 

[tojomojo]

I just tried it as I thought and the code below works OK but as I select the new staff member in E2 then their list gets populated OK but the previous members list sheet gets cleared.

I dont want this to happen as the other member could still be viewing their sheet.

How do I stop this? Current code is;

Private Sub Worksheet_Change(ByVal Target As Range)

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    With Sheets("Tasks")


        If Not Intersect(.Range("E2"), Target) Is Nothing Then
            Sheets("SD List").Cells.Delete


            If Target.Value = "SD" Then
                With .Range("E3:E" & .Range("E" & Rows.Count).End(xlUp).Row)
                    .AutoFilter 1, Target.Value
                    On Error Resume Next
                    .SpecialCells(12).EntireRow.Copy Sheets("SD List").Range("A1")
                    On Error GoTo 0
                    Sheets("SD List").Columns.AutoFit
                    .AutoFilter
                End With
            End If
        End If
        
        If Not Intersect(.Range("E2"), Target) Is Nothing Then
            Sheets("KG List").Cells.Delete


            If Target.Value = "KG" Then
                With .Range("E3:E" & .Range("E" & Rows.Count).End(xlUp).Row)
                    .AutoFilter 1, Target.Value
                    On Error Resume Next
                    .SpecialCells(12).EntireRow.Copy Sheets("KG List").Range("A1")
                    On Error GoTo 0
                    Sheets("KG List").Columns.AutoFit
                    .AutoFilter
                End With
            End If
        End If


    End With


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With


End Sub

 

[MARK858]

I can then duplicate this IF for each initial in the pulldown?

Am I right?

Why would you want to do this by hard coding the macro rather than looping through the list in sheet LOOKups (or creating an array from the values)?

 

[tojomojo]

Probably only because I don't know what 'looping through the list in sheet LOOKups (or creating an array from the values)' really is.....

I started down the macro route and it seems to be working OK. I am new to this so I am sure there is a better way.

It just seemed link a logical next step to have it not clear the sheet that someone else might have been referring to and just have it append (or overwrite) if they reselected their name on the Tasks sheet if new tasks were added.

I just dont know how to stop it clearing.

 

[MARK858]

Stopping it clearing is simply removing the Delete line.
Appending the data means rewriting it to append to the next blank row (and putting in a test/rewrite for the header).

Before I rewrite the code I need you to be sure exactly what you want the code to do as I don't want to spend time repeatedly rewriting it.

Please think about your end requirement and then post what you need.

 

[tojomojo]

Thanks. I will review it tonight and get back to you tomorrow.

Appreciate the help.

 

[tojomojo]

I think I have managed to work it out now. Thanks again for all the help.