[Fixed] Data Validation Not Working for Copy Paste in Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel Data Validation is an amazing feature that controls the data entered into the worksheet. At the time of entering new data, we can set any kind of condition according to our need on selected cells using Excel Data Validation. But usually, Excel Data Validation Not Working Copy Paste is a major problem in this procedure.

For more clarification, I am going to use a dataset of a company containing data of Employee Name, Department and employees who are in Waiting List.

Excel Data Validation Not Working Copy Paste


Problem and Solution of Data Validation Not Working Copy Paste in Excel

1. Reason for Data Validation Not Working Copy Paste in Excel

In this dataset, I am going to employ the Data Validation Feature in the Employ Name column to limit my entries.

Steps

  • For this, I have selected Column B which contains the Employee Name.
  • Then, from the Data tab, I have selected Data Tools and finally selected Data Validation from there.

A dialogue box will appear.

  • Here, the Settings tab is open in the dialogue box.
  • After that, we have to select Validation criteria from Allow. Here, I have chosen Text length.
  • Next, I have to limit the range for validation. Here, I have allowed the data containing text from a minimum of 1 to a maximum of 8 characters for validation.

Excel Data Validation Not Working Copy Paste

The Data Validation feature will be applied.
Followingly, I input data that does not satisfy the condition. Here, I wrote the value Labuchange from the Waiting List.

A warning message will be shown for the invalid data entry. As I input data that is invalid according to the Data Validation condition, it did not accept the value and a warning message has appeared.

Excel Data Validation Not Working Copy Paste

But if you copy the value and paste it in the data validated column, it will accept the value and no warning message will appear.

This is a serious problem as our Data Validation is not working on copy-paste.

Read More: How to Circle Invalid Data in Excel


2. Creating Data Validation Using VBA to Work Copy Paste

In the case of solving the Excel Data Validation Not Working for Copy Paste problem, Visual Basic for Applications (VBA) is the only solution. We are going to explain the solution here.

Steps :

  • First of all, select the Developer tab.
  • Next, select Visual Basic.

Excel Data Validation Not Working Copy Paste

A new window will appear.

  • Click on the Sheet in which you want to apply the Code. Here, I selected Sheet2 named VBA to apply the code.

  • Select the Worksheet from General and Change from Declarations to create a Private Sub.

Excel Data Validation Not Working Copy Paste

  • Now, input the following code on how you want to validate the data.

My used code is mentioned below:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then
        If Len(Target.Value) >= 8 Then
            MsgBox "Name is longer than 8. Undo!", vbCritical
            Application.Undo
        End If
    End If

End Sub

VBA to Create Data Validation

  • Then, check if the validation is working or not from the sheet.

Here, I have inserted the value from the D7 cell by copying and pasting it into B10. The value shows an Error alert according to the data validation condition. So, the warning box will appear.

Showing the Warning MsgBox

This method also works perfectly if I input the data through the keyboard or any other process.

If you click OK, the cell will go back to its previous mode.


Practice Part

You can practice here to be an expert.


Download Practice Workbook


Conclusion

Excel Data Validation Not Working Copy Paste problem can have a severe impact in so many important cases. I hope you will benefit from the solution. For any further questions related to the topic, comment below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Naimul Hasan Arif
Naimul Hasan Arif

Hello everyone, I am Naimul Hasan Arif, graduated from Bangladesh University of Engineering and Technology (BUET). I am working as an Excel and VBA Content Developer. I try to remain dedicated to my duties and give my best with my skills & knowledge.

11 Comments
  1. Great Help so far!

    What If I want Column A and B to have limit character of 8, but column C a limit of 10.

    How would you do it ?

    • Reply Avatar photo
      Naimul Hasan Arif Aug 16, 2022 at 9:16 AM

      Thanks for the appreciation.
      For columns A & B:
      From the Data tab, select Data Tools and pick Data Validation from there. This will open the Data validation feature. From there, mention the text length of minimum “1” to maximum “8”.

      For column C, follow the same procedure. Just in the maximum section, input “10”.

  2. Hello,

    What if I want column A to have a limit of 8 and Column B to have a limit of 10. How would I do that ?

    Thanks

    • Reply Avatar photo
      Naimul Hasan Arif Aug 16, 2022 at 9:17 AM

      Hi,

      For columns A:
      From the Data tab, select Data Tools and pick Data Validation from there. This will open the Data validation feature. From there, mention the text length of minimum “1” to maximum “8”.

      For column B, follow the same procedure. Just in the maximum section, input “10”.

  3. Hello, I have follwed the steps above and am running into an issue where the error message continues to “loop” and doesn’t let me change the text that is over the character limit. Is there a way to let a user go back and edit after the error message appears?

  4. Hey Cole, did the loop issue get sorted ? i am also facing the same issue !

  5. Hi dear NAIMUL HASAN ARIF, this article is the great and very helpful. Thank you for it.

    But I think that in your code have some misunderstanding in here “Private Sub Worksheet_SelectionChange(ByVal Target As Range)”.
    I think it should be like that “Private Sub Worksheet_Change(ByVal Target As Range)”. Because when I click the cell of the range which I choose in VBA code, excel show error message in the VBA code.

    • Reply Avatar photo
      Naimul Hasan Arif May 28, 2023 at 12:41 PM

      Dear TURAN,
      Thanks for your valuable comment. That was an honest mistake from me. I have updated the error. I have also modified the code as I have found a simpler way to perform the same task.

      Best Regards,
      Naimul Hasan Arif

  6. What if I want to put data validation for duplicate values. Can I get the code for that ?

    • Dear Anas,
      You have to write the following code to get data validation for duplicate values, you have write the code below.

      
      Sub CreateUniqueDropDownListInC12()
          Dim ws As Worksheet
          Dim cell As Range
          Dim dict As Object
          Dim dataValidation As Validation
          Dim dvList As String
          
          ' Define the worksheet where you want to apply the data validation
          Set ws = ActiveSheet
          
          ' Create a dictionary object to store unique values
          Set dict = CreateObject("Scripting.Dictionary")
          
          Application.ScreenUpdating = False
          
          ' Loop through the specified range (C5 to C11)
          For Each cell In ws.Range("C5:C11")
              If cell.Value <> "" Then ' Check for non-empty cells
                  If Not dict.Exists(cell.Value) Then
                      dict(cell.Value) = 1 ' Add the value to the dictionary
                  End If
              End If
          Next cell
          
          ' Convert the unique values to a comma-separated string for data validation
          dvList = Join(dict.Keys, ",")
          
          ' Apply data validation with a dropdown list to cell C12
          Set dataValidation = ws.Range("C12").Validation
          With dataValidation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=dvList
              .IgnoreBlank = True
              .InCellDropdown = True
              .ShowInput = True
              .ShowError = True
          End With
          
          Application.ScreenUpdating = True
      End Sub
      

      Then we get the data values of unique departments Marketing and Sales.
      datavalidation with duplicates
      With regards,
      Joyanta Mitra

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo