CODEWITHSUNDEEP
excelvba
FancyDolphin
FancyDolphin

Reputation: 457

How to copy and paste and retain the data validation?

The data is 100% right.

I want to paste the data into the cell with data validation and keep the data validation.

Upvotes: 1

Views: 10666

Answers (2)

Gerhard Powell
Gerhard Powell

Reputation: 6175

Here is a VBA example of how to add the validation back after any change:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Range("Table1[Column1]").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, Operator _
        :=xlBetween, Formula1:="=INDIRECT(""Table2"")"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
    End With
End Sub

Upvotes: 1

TechnoDabbler
TechnoDabbler

Reputation: 1265

You don't give any context. I assume you're copying with VBA. If your code copies and then uses .PasteSpecial with a Paste type of xlPasteValues, formatting of the destination cell (including data validation) will be retained ... an example being ...

Public Sub test()
    Worksheets("Sheet1").Range("A1").Copy
    Worksheets("Sheet1").Range("B1").PasteSpecial (xlPasteValues)
    Worksheets("Sheet1").Range("B1").PasteSpecial (xlPasteValidation)
End Sub

Also refer to: Paste vs PasteSpecial

Upvotes: 2

Related Questions