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]

SpecialCells seems to find it.......and reports the right pull down list item

the debugger says

Run-time error '9'
Subscript out of range

I removed the .Cells items and it is still the same.

 

[MARK858]

That probably means your Target.Value does not exactly match your sheet name.

 

[tojomojo]

I've tried renaming the sheets to just KG etc. (after changing the specialcell line). Still no good.

Will post excel and VBA later.

Thanks for the help.

 

[tojomojo]

code and link to excel below.

Target seems to pick up fine but it still crashes. Sheet names appear exactly the same....

Private Sub Worksheet_Change(ByVal Target As Range)        
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub


Application.ScreenUpdating = False
Application.EnableEvents = False


With Range("C3", Range("C" & Rows.Count).End(xlUp))
        .AutoFilter 1, Target.Value
        Sheets(Target.Value).Cells.Clear
'        .SpecialCells(12).EntireRow.Copy Sheets(Target.Value).[A4]
        .SpecialCells(12).EntireRow.Copy Sheets(Target.Value & " list").[A4]
        Sheets(Target.Value).Columns.AutoFit
        .AutoFilter
End With


Application.EnableEvents = True
Application.ScreenUpdating = True
 
End Sub

http://ge.tt/2aMNMYu2

 

[vcoolio]

Hello Tojomojo,

I haven't had a look at your sample file but if your values in the A1 drop down don't exactly match the sheet names, then see if the following modification to the code works:-

Private Sub Worksheet_Change(ByVal Target As Range)
        
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

With Range("C3", Range("C" & Rows.Count).End(xlUp))
        .AutoFilter 1, Target.Value   '------> Target values as per drop down.
        Sheets(Target.Value & " list").Cells.Clear  '------> Target values to match sheet names.
        .SpecialCells(12).EntireRow.Copy Sheets(Target.Value & " list").[A4]  '------> Target values to match sheet names.
        Sheets(Target.Value & " list").Columns.AutoFit  '------> Target values to match sheet names.
        .AutoFilter
End With

Application.EnableEvents = True
Application.ScreenUpdating = True
 
End Sub

Cheerio,
vcoolio.

 

[tojomojo]

Seems to work thanks.

I obviously wasn't declaring the Target.Value & " list" in enough places.....

Thanks again

 

[vcoolio]

You're welcome Tojomojo. Glad to have been able to assist.
Thanks for the feed back also.

Cheerio,
vcoolio.