Copying data validation from one worksheet to another in an excel workbook

0
votes

Ive got three worksheets setup.

The first contains reference data for my data validation list.

The second references the first sheet for a data validation drop down list. Something like sheet1!$a1:$a3

The problem arises when I try and copy the cell in sheet two that contains the data validation drop down list to a cell in sheet 3 where the destination cell is empty.

I'm trying to copy excel data validation but when copied across to sheet 3 it references the cells $a1:$a3 but doesn't maintain the reference to sheet1 where the data for the drop down list is actually stored.

How can I copy the data validation such that when it is copied to sheet 3 it still maintains the reference to sheet1 where the static data that makes up the list is.

1
excelvba
Can you provide the code that is performing the copy? – Zac
I'm performing the copy manually. Ctrl + c then Ctrl + v on the other sheet – yoshiserry
Try changing your reference to data validation to something like: sheet1!$a$1:$a$3 – Zac
Which version of Excel are you using? This is only possible since Excel version 2010+ – Ralph
Hope I understood question correctly. Name your range for data validation as Worksheets("Sheet1").Range("A1","A3").Name="DataValid" and reference to this named range. It won't matter after this to which sheets you copy cells with validation. – AntiDrondert

1 Answers

0
votes

If I am understanding you correctly, the pasted Data Validation list is not showing the corrrect information. In my workbook I needed multiple cells of DV to display info from the sheet labled "Parts" after they were copied to the sheet labled "Ascending".

What I had to do was select my newly, and thus far incorrectly, pasted material, go to the 'Data' tab and click into 'Data Validaion --> Data Validation' (drop down menu). From here I had to edit the source to reflect the origional sheet.

Was: =$C$4:$C$21

Edit: =Parts!$C$4:$C$21

This determines what sheet the data is being validated from. Once Ive done this, I can copy and paste a DV interal to whatever sheet I am working on.

For some reason naming the range from the origional sheet (Parts) did not carry over in the pasted DV. VLOOKUP and other formulas thankfully work fine.