VBA based off of Data Validation List

[lpw0806]

Hi There --

I have a list of 4 values ("Auto", "Multi", "Tech", "Lifestyle") in a data validation list (cell I3, Sheet = "Opportunities"). I'd like the macro below to run / pull data based on that selection. Right now the macro searches through the Raw Data and pulls each of the categories one at a time. I want it to pull based on cell I3 in the current sheet.

Sorry in advance if this is a dumb question - I am still learning VBA and am new! Thank you for your help!

Sub Opportunities()
'
' Opportunities Macro


' Clear Data from Sheet
Sheets("Opportunities").Cells.Clear




' Filter Seller POD
Dim ary As Variant
Dim i As Long
ary = Array("Auto", "Multi", "Tech", "Lifestyle")

For i = 0 To UBound(ary)
With Sheets("Raw Data")
If .AutoFilterMode Then .AutoFilterMode = False
With .Range("A1:W1")
.AutoFilter 11, ">=" & 75
.AutoFilter 19, ary(i)
End With




Intersect(.AutoFilter.Range, .Range("A:A,C:C,S:S")).Copy Sheets("Opportunities").Range("A1")
.AutoFilterMode = False
End With
Next i


End Sub

 

[dmt32]

Hi,

Maybe Advanced filter will do what you want.

Based on information you posted here:https://www.mrexcel.com/forum/excel...acro-output-data-into-tab-based-criteria.html

try following:

Place following in your Opportunities worksheet CODE PAGE

Private Sub Worksheet_Activate()
    BuildFilterSheet Me
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exitsub
    If Target.Count > 1 Then Exit Sub
    If Target.Address = Me.Range("I3").Address Then


        Application.EnableEvents = False
        With Sheets("Filter")
            .Range("A2").Value = ">=75"
            .Range("B2").Value = Me.Range("I3").Value
        End With
            
'copy filtered data to sheet
       Sheets("Raw Data").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                                         CriteriaRange:=Sheets("Filter").Range("A1:B2"), _
                                         CopyToRange:=Me.Range("A1:D1")
    Me.Columns("A:D").EntireColumn.AutoFit
    End If
exitsub:
    Application.EnableEvents = True
End Sub

Place following in a STANDARD module

Sub BuildFilterSheet(ByVal sh As Object)
    Dim wsFilter As Worksheet
    
    On Error GoTo exitsub
    Application.EnableEvents = False
'add filter sheet
    If Not Evaluate("ISREF('" & "Filter" & "'!A1)") Then
        With ThisWorkbook
            .Worksheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Filter"
        Set wsFilter = .Sheets("Filter")
        End With
    
    With ThisWorkbook.Worksheets("Raw Data")
'add required field headers to opportunities sheet
        sh.Range("A1:D1").Value = Array(.Range("C1").Value, .Range("A1").Value, _
                                        .Range("Q1").Value, .Range("M1").Value)
'data validation header
        sh.Range("I2").Value = .Range("S1").Value
'add required field headers to filter sheet
        wsFilter.Range("A1:B1").Value = Array(.Range("K1").Value, .Range("S1").Value)
    End With
    
    sh.Activate
    
'add data validation
    With sh.Range("I3")
        .Interior.Color = vbYellow
        With .Validation
'delete previous validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="Lifestyle,Multi,Tech,Auto"
        End With
        .Select
    End With
  End If
exitsub:
    Application.EnableEvents = True
End Sub

When you select the opportunities sheet, a new sheet (Filter) will be added which is needed for the advanced Filter.
Also, the required Field Headings from Raw Data sheet will be added as well as validation list.

Hope Helpful

Dave

 

[lpw0806]

Thank you so much. I really appreciate the help. But to be honest, I am not totally sure what you mean. What is a code page vs. standard module?? I have tried researching and am not finding anything helpful that explains this.

Thanks again!

 

[dmt32]

Thank you so much. I really appreciate the help. But to be honest, I am not totally sure what you mean. What is a code page vs. standard module?? I have tried researching and am not finding anything helpful that explains this.

Thanks again!

Hi,

for worksheet code page - Right Click the required Sheet Tab & from menu select View Code. This takes you to the sheets code page - paste worksheet code here.

For Standard Module From VB Editor Select from Menu Insert > Module. This inserts a new standard module - paste module code here.

Dave

 

[lpw0806]

Thanks --

What do I do once I've completed the steps above? How do I get it to run?

 

[lpw0806]

Oh wait! I figured it out! Amazing!!!

THANK YOU!!!

Last question --- if I want to sum column C in the output (column Q in raw data) based on the "Opportunity Name". How would I do that? Like a pivot table

if I want to sum the output in

 

[dmt32]

Oh wait! I figured it out! Amazing!!!

THANK YOU!!!

Last question --- if I want to sum column C in the output (column Q in raw data) based on the "Opportunity Name". How would I do that? Like a pivot table

if I want to sum the output in

try adding the following after the filter

'copy filtered data to sheet
       Sheets("Raw Data").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                                         CriteriaRange:=Sheets("Filter").Range("A1:B2"), _
                                         CopyToRange:=Me.Range("A1:D1")
                                         
    lr = Me.Range("C" & Rows.Count).End(xlUp).Row
    With Me.Range("C" & lr + 1)
        .Value = Application.Sum(Me.Range("C2:C" & lr))
        .NumberFormat = "[$$-409]#,##0"
    End With

Adjust the Number Format to suit your local settings as required.


Dave