I'm having a wee issue copying cells from one Worksheet to another in Excel VBA. The Source Sheet contains an individual client record stored in a single row. The Destination Sheet is a collection of such records, stacked row by row. It's important to keep them separate in for confidentiality.

I've built a macro to copy Source into the Destination Workbook, copy cells from Source, and paste them into the first empty row of the Destination Sheet. Since they both contain the same information, I've applied the same Data Validation to corresponding cells in each Sheet, mostly drop down lists.

However, the Source and Destination Sheets originate in different Workbooks. When I copy cells from Source to Destination, the Data Validation comes with. The cells pasted in Destination now refer to cells in Source to populate their drop down lists. The link usually breaks, breaking the drop down, and prompting the user each time Destination is opened.

What I'd like to do is copy the values in Source cells without copying the Data Validation. Like I said earlier, the Data Validation is the same in each Sheet (except that they refer to Sheets different Workbooks), so all the values should validate anyway.

Here is a sample of my copying code:

Sub copyNoValidation(wkbDestination as ThisWorkbook, oRange as Range, cellDest As Range)
    cellDest.PasteSpecial xlPasteValues
End Sub

Thanks for your help!


It is easy to copy and then remove any DV in the destination:

Sub KopyPaste()
    Dim r1 As Range, r2 As Range
    Set r1 = Range("A1")
    Set r2 = Range("A2")
    r1.Copy r2
End Sub
  • Is there a way to do this while maintaining the DV in the destination? I want to replace the source DV with the dest DV. – mykrus Oct 26 '18 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.