Copy Paste Drop-Down Data using Excel VBA

0
votes

I want to use VBA code in my Excel spreadsheet to copy data from one column to another. I have both checkboxes and drop-down lists. I have worked out how to copy the ticked checkboxes by copying the adjacent cell containing the TRUE and FALSE data using the following code:

Range("B1:B9").Copy Range("D1:D9")

However, the data in the drop-downs does not copy over.What do I need to add to my code to copy and paste this information?

2
excelvbadrop-down-menu
How are the drop-downs defined and created? What range is being used? – PeterT
@PeterT, The drop-downs are created through Data, Data Validation, Settings ,List. The range is cells B10:B15. – Prezza
I'm still not understanding. If your drop-down data is in B10:B15 (and it won't be changed by code similar to what you've shown), then "data in drop-downs does not copy over." Which drop-downs, in what range? – PeterT
Sorry @PeterT I meant the checkboxes are in A1:A9 with TRUE/FALSE in B1:B9. The drop downs are in B10:B15. The copy paste code words for cells A1:A9 but not for B10:B15. – Prezza

2 Answers

0
votes

If your dropdown is created in Range("A1") and your data validation cells are defined at B1:B9 then you need to change the data validation definition from Source =$B$1:$B$9 to =B1:B9. If you then copy the dropdown and the data from "A1:B9" to "C1:D9" then the dropdown itself and also the data defintion is copied.

Range("A1:B9").Copy Range("D1")

After changing the Cells D1 to D9 to different values you will recognize that the new dropbox now refers to range("D1:D9").

For iCt = 1 to 9 : Range("D1").Offset(iCt,0).Value = iCt + 10 : next iCt

;-)

0
votes

I added in the following to copy and paste the dropdowns:

Range("B10:B15").Select
    Selection.Copy
    Range("D10:D15").Select
    ActiveSheet.Paste

Thank you to those who answered my question and helped me to get to an answer that worked.