0

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)
    wkbDestination.Worksheets("Source").Range(oRange).Copy
    cellDest.PasteSpecial xlPasteValues
End Sub

Thanks for your help!

2 Answers 2

1

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
    r2.Validation.Delete
End Sub
1
  • 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, 2018 at 16:46
0

Use PasteSpecial method.

Dim firstSheet As Range, secondSheet As Range
Set firstSheet = Range("A1")
Set secondSheet = Range("A2")
firstSheet.Copy 
secondSheet.PasteSpecial Paste:=xlPasteValues

Reason: When you normally copy paste validations, the target cell will retain the old validation for that you use the second option to paste only the values and not including the validations. This is what is done using VBA code.

enter image description here

2
  • Can you explain why?
    – Toto
    Oct 12, 2021 at 9:46
  • @Toto have edited the reason :-)
    – Nithya
    Oct 13, 2021 at 18:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .