INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Copy, Paste driven by dropdown

Copy, Paste driven by dropdown

(OP)
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:

CODE --> vba

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

CODE --> vba

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?

RE: Copy, Paste driven by dropdown

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:
If ws. Range(dd.ListFillRange).Cells(dd.Value) = "JUNE" then ' assuming all data in the active sheet
or
If dd.value = 6 then

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

combo

RE: Copy, Paste driven by dropdown

(OP)
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.

RE: Copy, Paste driven by dropdown

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

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close