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

com98104

New Member
Joined
Jan 14, 2019
Messages
4
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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)

Code:
[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]
 
Upvote 0
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.
Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top