CODEWITHSUNDEEP
excelvbadrop-down-menu
Prezza
Prezza

Reputation: 19

Copy Paste Drop-Down Data using Excel VBA

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?

Upvotes: 0

Views: 5167

Answers (2)

Prezza
Prezza

Reputation: 19

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.

Upvotes: 0

simple-solution
simple-solution

Reputation: 1139

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

;-)

Upvotes: 0

Related Questions