Copy, Paste driven by dropdown

[Igawa29]

So I am trying to have my Excel workbook copy and paste values when the value changes in my dropdown box. I was hoping someone could help me understand how to connect my code.

So far I have the following:

Dim ws As Worksheet
Dim dd As DropDown

Set ws = ActiveSheet
Set dd = ws.Shapes("Drop Down 1").OLEFormat.Object

Dim DPK_ALL_TOTAL_EAST_NUM, DPK_ALL_TOTAL_WEST_NUM, DPK_ALL_TOTAL_CMB_NUM, DPK_ALL_TOTAL_Trend_NUM, DPK_IFP_TOTAL_EAST_NUM, DPK_IFP_TOTAL_CMB_NUM, DPK_IFP_TOTAL_Trend_NUM, DPK_SELECT_TOTAL_EAST_NUM, DPK_SELECT_TOTAL_WEST_NUM, DPK_SELECT_TOTAL_CMB_NUM, DPK_SELECT_TOTAL_Trend_NUM As Range



Set DPK_ALL_TOTAL_EAST_NUM = Range("Q52:Q53")
Set DPK_ALL_TOTAL_WEST_NUM = Range("R52:R53")
Set DPK_ALL_TOTAL_CMB_NUM = Range("S52:S53")
Set DPK_ALL_TOTAL_Trend_NUM = Range("Q55:S55")

Set DPK_IFP_TOTAL_EAST_NUM = Range("Q58:Q59")
Set DPK_IFP_TOTAL_CMB_NUM = Range("S58:S59")
Set DPK_IFP_TOTAL_Trend_NUM = Range("Q61:S61")

Set DPK_SELECT_TOTAL_EAST_NUM = Range("Q65:Q66")
Set DPK_SELECT_TOTAL_WEST_NUM = Range("R65:R66")
Set DPK_SELECT_TOTAL_CMB_NUM = Range("S65:S66")
Set DPK_SELECT_TOTAL_Trend_NUM = Range("Q68:S68")


Set JUN_DPK_ALL_TOTAL_EAST_NUM = Range("Source!AG202:AG203")
Set JUN_DPK_ALL_TOTAL_WEST_NUM = Range("Source!AH202:AH203")
Set JUN_DPK_ALL_TOTAL_CMB_NUM = Range("Source!AI202:AI202")
Set JUN_DPK_ALL_TOTAL_Trend_NUM = Range("Source!AG205:AI205")

Set JUN_DPK_IFP_TOTAL_EAST_NUM = Range("Source!AG208:AG209")
Set JUN_DPK_IFP_TOTAL_CMB_NUM = Range("Source!AI208:AI209")
Set JUN_DPK_IFP_TOTAL_Trend_NUM = Range("Source!AG211:AI211")

Set JUN_DPK_SELECT_TOTAL_EAST_NUM = Range("Source!AG215:AG216")
Set JUN_DPK_SELECT_TOTAL_WEST_NUM = Range("Source!AH215:AH216")
Set JUN_DPK_SELECT_TOTAL_CMB_NUM = Range("Source!AI215:AI216")
Set JUN_DPK_SELECT_TOTAL_Trend_NUM = Range("Source!AG218:AI218")

So I want to say something like

If dd.value = "JUNE" then
DPK_ALL_TOTAL_EAST_NUM.ClearContents
DPK_ALL_TOTAL_WEST_NUM.ClearContents 
DPK_ALL_TOTAL_CMB_NUM.ClearContents
DPK_ALL_TOTAL_Trend_NUM.ClearContents

DPK_IFP_TOTAL_EAST_NUM.ClearContents
DPK_IFP_TOTAL_CMB_NUM.ClearContents
DPK_IFP_TOTAL_Trend_NUM.ClearContents

DPK_SELECT_TOTAL_EAST_NUM.ClearContents
DPK_SELECT_TOTAL_WEST_NUM.ClearContents
DPK_SELECT_TOTAL_CMB_NUM.ClearContents 
DPK_SELECT_TOTAL_Trend_NUM.ClearContents


Set JUN_DPK_ALL_TOTAL_EAST_NUM.Copy
DPK_ALL_TOTAL_EAST_NUM.Paste 
Set JUN_DPK_ALL_TOTAL_WEST_NUM.Copy
DPK_ALL_TOTAL_WEST_NUM.Paste 
Set JUN_DPK_ALL_TOTAL_CMB_NUM.Copy
DPK_ALL_TOTAL_CMB_NUM.Paste
Set JUN_DPK_ALL_TOTAL_Trend_NUM.Copy
DPK_ALL_TOTAL_Trend_NUM.Paste
Set JUN_DPK_IFP_TOTAL_EAST_NUM.Copy
DPK_IFP_TOTAL_EAST_NUM.Paste 
Set JUN_DPK_IFP_TOTAL_CMB_NUM.Copy
DPK_IFP_TOTAL_CMB_NUM.Paste
Set JUN_DPK_IFP_TOTAL_Trend_NUM.Copy
DPK_IFP_TOTAL_Trend_NUM.Paste
Set JUN_DPK_SELECT_TOTAL_EAST_NUM.Copy
DPK_IFP_TOTAL_Trend_NUM.Paste
Set JUN_DPK_SELECT_TOTAL_WEST_NUM.Copy
DPK_SELECT_TOTAL_WEST_NUM.Paste 
Set JUN_DPK_SELECT_TOTAL_CMB_NUM.Copy
DPK_SELECT_TOTAL_CMB_NUM.Paste
Set JUN_DPK_SELECT_TOTAL_Trend_NUM.Copy
DPK_SELECT_TOTAL_Trend_NUM.Paste

But for some reason I can't get the dropdown box to trigger this to happen. Any ideas?

 

[combo]

You use Forms control, here Value property returns index of selected item in the linked list (base 1). You need to refer to source range contents (ListFillRange) or to known index:
[tt]If ws. Range(dd.ListFillRange).Cells(dd.Value) = "JUNE" then ' assuming all data in the active sheet[/tt]
or
[tt]If dd.value = 6 then[/tt]

In your declaration [tt]Dim DPK_ALL_TOTAL_EAST_NUM, DPK_ALL_TOTAL_WEST_NUM,...[/tt] only last item is declared as Range, other are variants.

combo

 

[Igawa29]

Thank you combo I will give this a try. Thanks for the tip about the ranges too, I will go ahead and separate all those out into separate DIM statements first.

 

[combo]

It's possible with one Dim, but requires type after each variable:
[tt]Dim DPK_ALL_TOTAL_EAST_NUM As Range, DPK_ALL_TOTAL_WEST_NUM As Range[/tt]

combo