Excel Drop Down/ Autopopulate

[Philip]

Hey!

I have a drop down menu with certain values (in my case, one of the drop
downs is titled "Billable"). I am trying to figure out how I can create a
formula which searches for all items labeled "Billable" and then pulls a
range of data associated with that label and populates only that data into a
new worksheet.

Do you have any advice on how I can do this?

Thank you so much for your help,

Philip

 

[Don Guillett]

How about just using
data>filter>autofilter>filter on "billable">copy/paste. Record a macro if
often.

 

[Philip]

I'm sorry, the autofilter for billable doesn't truly perform the function
that I am looking for.

Thank you for your help.

Philip

 

[Don Guillett]

If desired, send your workbook to my address below with these msgs and a
complete explanation.

 

[Don Guillett]

Both Autofilter or a loop will work.

Sub useautofilter() 'SalesAidSoftware
Dim lr As Long
Dim mc As String
Application.ScreenUpdating = False
mc = "U"
lr = Cells(Rows.Count, mc).End(xlUp).Row
Range("a2:x" & lr).AutoFilter Field:=21, Criteria1:="Billable"
Range("a3:x" & lr).Copy
Sheets("Tab1 Destination").Range("a3").PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone
Range("a2:x" & lr).AutoFilter
Application.ScreenUpdating = True
End Sub

Sub copybillable() 'SalesAid Software
Dim mc As String
Dim i, lr, dlr As Long
mc = "u"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For i = 2 To lr
With Sheets("Tab1 Destination")
dlr = .Cells(Rows.Count, mc).End(xlUp).Row + 1
If UCase(Cells(i, mc)) = "BILLABLE" Then
.Rows(dlr).Value = Rows(i).Value
End If
End With
Next i
End Sub

 

[Don Guillett]

Actually, I like this even better
Sub useautofilter() 'SalesAidSoftware
Dim lr As Long
Dim mc As String
Application.ScreenUpdating = False
mc = "U"
lr = Cells(Rows.Count, mc).End(xlUp).Row
With Range("a2:x" & lr)
..AutoFilter Field:=21, Criteria1:="Billable"
..Offset(1).Copy
Sheets("Tab1 Destination").Range("a3").PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone
Application.CutCopyMode = False
..AutoFilter
End With
Application.ScreenUpdating = True
End Sub