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

 

[vcoolio]

Hello Tojomojo,

I've just re-joined the conversation but I'm glad to see that Mark has helped you along your way and its good to see that you've managed to sort it out.

Based on a couple of your earlier posts, and in case you've not quite got it right, Mark's code, modified a little and placed in the "Tasks" sheet module may help you further:-

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

Application.ScreenUpdating = False
Application.EnableEvents = False

With Range("E3:E" & Range("E" & Rows.Count).End(xlUp).Row)
        .AutoFilter 1, Target.Value
        .SpecialCells(12).EntireRow.Copy Sheets(Target.Value).[A1]
        Sheets(Target.Value).Columns.AutoFit
        .AutoFilter
End With

Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

You will need to create sheets for each individual first and name them exactly as per the drop down in E2 (SD, KG, etc...).

The code will transfer each relevant row of data to its individual sheet.

This code does not delete anything from the "Tasks" sheet but will update the relevant individual sheet when a transfer of data occurs.

Test it in a copy of your actual workbook first and clear out any other code that you may already have.

I hope that this helps.

Cheerio,
vcoolio.

 

[MARK858]

Why are you using

Application.CutCopyMode = False

when the code uses destination destination doesn't put it into CutCopyMode (notice no marching ants around the range being copied) and evaluate is slower than directly referencing the range.

You should also always use error handling when using specialcells as the code will crash if no matches are found.

 

[vcoolio]

Why are you using

Application.CutCopyMode = False

Its habit more than anything. Whenever I type "copy" I see marching ants without thinking anything more of it. Its a trivial matter that doesn't affect the performance of the code.

...and evaluate is slower than directly referencing the range.

In a situation like the OP's, where you may regularly have only one, two or three rows to transfer, we're talking picoseconds of difference (even a with a larger number of rows). Plus, I find using evaluate to be a sleeker method. I suppose it boils down to personal preference.

You should also always use error handling when using specialcells as the code will crash if no matches are found.

True. However, in a case like this, where the code doesn't delete anything from the master sheet, the matches will always be there. It would be a different story should data be deleted from the master sheet. The OP here doesn't appear to want data deleted from the master sheet.

Cheerio,
vcoolio.

 

[MARK858]

It

True. However, in a case like this, where the code doesn't delete anything from the master sheet, the matches will always be there. It would be a different story should data be deleted from the master sheet. The OP here doesn't appear to want data deleted from the master sheet.
.

It has nothing to do with deleting, it has to do with matching the the criteria of the specialcells. If (as the OP is thinking about) they append the data then they won't want the header row after the first copy.

If the header row is omitted and they chose TB in their dropdown the code will error as there is no match and so no visible cells.

Its a trivial matter that doesn't affect the performance of the code.

So it is sleeker to use the evaluate but trivial to add in a line of unnecessary code... interesting

 

[vcoolio]

@ Mark:-

So it is sleeker to use the evaluate but trivial to add in a line of unnecessary code... interesting

From your code:

With Sheets("Tasks")

Not that I care as it is a trivial matter, but you're referencing the main sheet when the code is in the main sheet module!...........interesting.

Alas, no one is perfect!

Cheerio,
vcoolio.

 

[tojomojo]

Sorry just got round to looking at this again....in post #21

Do I put "Tasks" as the target here? I cant seem to get this to run....

[COLOR=#333333]If Intersect(Target, Range("E2")) Is Nothing Then Exit Sub[/COLOR]

 

[vcoolio]

Hello Tojomojo,

No. E2 is where you have your drop down selections. When you select a value from the drop down, say SD, the code will filter Column E for any SD value and transfer the relevant rows of data to the SD sheet. You'll need to create the sheets first (SD, KG, OM...etc.).

Cheerio,
vcoolio.

 

[tojomojo]

So my whole code looks like this now and basically functions

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:C" & Range("C" & Rows.Count).End(xlUp).Row)
        .AutoFilter 1, Target.Value
        .SpecialCells(12).EntireRow.Copy Sheets("KG list").[A4]
        Sheets("KG list").Columns.AutoFit
        .AutoFilter
        
 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub


Application.ScreenUpdating = False
Application.EnableEvents = False


With Range("C3:C" & Range("C" & Rows.Count).End(xlUp).Row)
        .AutoFilter 1, Target.Value
        .SpecialCells(12).EntireRow.Copy Sheets("SD list").[A4]
        Sheets("SD list").Columns.AutoFit
        .AutoFilter
            
End With
       If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub


Application.ScreenUpdating = False
Application.EnableEvents = False


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


 End With
       
        
End With


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

The only thing that doesnt work well is;

If I change the C column data to someone else's initials on the pull down then reselect A1 to update a sheet for an individual, ideally I want all the users list sheets to reflect the change to the list so everyone just gets their tasks updated. It isnt quite refreshing properly. Excel is attached here;
http://ge.tt/1BFBrSu2

 

[MARK858]

I am nowhere near a computer to post any code but have a look at Select Case in the link below to see one way around the it.

https://www.techonthenet.com/excel/formulas/case.php

 

[tojomojo]

I think what it needs to do on selecting the pulldown initial in A1 is first clear the cells in the target sheet then copy. When I do this manually it works OK. Just gotta work out the VB now.