VBA help needed to populate report based on 2 different drop down selections

[com98104]

Greetings all,
First off, Im great with formulas and even sql but clueless in vba. As such, I have a report I need to recreate urgently, which has 2 drop down menu boxes that populate the underlying spreadsheet.( The actual data is stored in subsequent tabs and static) Also, there are 9 total combinations of conditions. I have no clue where to begin but I assume the following: I would only need to assign a Macro to one drop down list box and I could just copy and paste ranges based on an if statement of some sort. Any help would be much appreciated. Also willing to pay for some help too.

Here are the conditions if I were to copy the data.
Condition1: IF Drop box1 value = Product 1 and Drop Box2 value = Metric 1 then
Copy Paste Special Values
(From: Tab3Wk D2:BD3 To: Report 1 F11:BF12
From: Tab2Wk C2:D9 To: Report 1 C14:D21
From: Tab2Wk E2:BE9 To: Report 1 F14:BF21
From: Tab1Wk C2:E45 To: Report 1 B28:D71
From: Tab1Wk F2:BF45 To: Report 1 F28:BF71 )

Condition2:Condition1 IF Drop box1 value = Product 2 and Drop Box2 value = Metric 1 then
Copy Paste Special Values(From: Tab3Wk D4:BD5 To: Report 1 F11:BF12
From: Tab2Wk C10:D17 To: Report 1 C14:D21
From: Tab2Wk E10:BE17 To: Report 1 F14:BF21
From: Tab1Wk C46:E89 To: Report 1 B28:D71
From: Tab1Wk F46:BF89 To: Report 1 F28:BF71 )

So forth and so on.

 

[AlphaFrog]

I suggest assigning the macro to a button. The user could change one or both dropdowns before copying the data.

In the code below, the dropdowns are in cells A1 and B1. You can change them to suit.

The Select-Case code block is similar to an If-ElseIf-End If code block. You can add more Cases.
How to use the CASE Statement (VBA)

[color=darkblue]Sub[/color] Copy_Data()
    [color=darkblue]Dim[/color] DB1 [color=darkblue]As[/color] Range, DB2 [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] DB1 = Range("A1")   [color=green]'Dropbox1[/color]
    [color=darkblue]Set[/color] DB2 = Range("B1")   [color=green]'Dropbox2[/color]
    
    [color=darkblue]Select[/color] [color=darkblue]Case[/color] [color=darkblue]True[/color]
        [color=darkblue]Case[/color] DB1 = "Product 1" And DB2 = "Metric 1"
            Sheets("Report 1").Range("F11:BF12").Value = Sheets("Tab3Wk").Range("D2:BD3").Value
            Sheets("Report 1").Range("C14:D21").Value = Sheets("Tab2Wk").Range("C2:D9").Value
            Sheets("Report 1").Range("F14:BF21").Value = Sheets("Tab2Wk").Range("E2:BE9").Value
            Sheets("Report 1").Range("B28:D71").Value = Sheets("Tab1Wk").Range("C2:E45").Value
            Sheets("Report 1").Range("F28:BF71").Value = Sheets("Tab1Wk").Range("F2:BF45").Value
            
        [color=darkblue]Case[/color] DB1 = "Product 2" And DB2 = "Metric 1"
            Sheets("Report 1").Range("C14:D21").Value = Sheets("Tab2Wk").Range("C10:D17").Value
            Sheets("Report 1").Range("F14:BF21").Value = Sheets("Tab2Wk").Range("E10:BE17").Value
            Sheets("Report 1").Range("B28:D71").Value = Sheets("Tab1Wk").Range("C46:E89").Value
            Sheets("Report 1").Range("F28:BF71").Value = Sheets("Tab1Wk").Range("F46:BF89").Value
        
    [color=darkblue]End[/color] [color=darkblue]Select[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Kenneth Hobson]

Welcome to the forum!

If you meant dropdowns as in Form dropdown (combobox-like) controls, I would do it similar to what AlphaFrog posted in #2 .

Change the parts to suit In mine, the first dropbox control has values from 1 to 10. The 2nd control has values a to z.

Sub dbAction()
  Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
  Dim db1 As DropDown, db2 As DropDown
  Dim d1, d2
  
  Set ws1 = Worksheets("Sheet1")
  Set ws2 = Worksheets("Sheet2")
  Set ws3 = Worksheets("Sheet3")
  Set db1 = ws1.DropDowns("Drop Down 1")
  Set db2 = ws1.DropDowns("Drop Down 5")
  d1 = db1.List(db1)
  d2 = db2.List(db2)
  
  Select Case True
    Case d1 = 1 And d2 = "a"
      MsgBox "Case1"
    Case d1 = 1 And d2 = "b"
      MsgBox "Case2"
    Case Else
  End Select
End Sub

 

[com98104]

wow you guys are awesome. going to try this out. Thanks so much for the quick replies

 

[com98104]

Thanks AlphaFrog. This might be my go to now. Have been using Excel for almost 20 years and just now realizing how much I missed by not learning VBA. Would have saved me so much time.

 

[com98104]

IT worked 100%. Button was a good call. thanks again