Data Validation Copier: Set Up Validation Once, Propagate to Every Entity Sheet

TL;DR: You spent 20 minutes setting up dropdown lists, date restrictions, and numeric limits on Entity-A’s schedule. Now Entity-B through Entity-L need the exact same validation in the exact same cells. This macro copies every rule from a template range to any sheets you choose. Two clicks, zero mistakes.

The Problem

It’s March 10th and the Henderson multi-entity consolidation is due Friday. You have 12 entities, each with a separate sheet — same columns, same rows, same structure. You carefully set up data validation on Entity-A: dropdowns for the tax treatment column, date restrictions for placed-in-service dates, whole-number limits for account codes. It took 18 minutes to get every rule right, including the input messages and error alerts.

Now you face the other 11 sheets. Same 50 rows of the same 3 columns of the same rules, set up 11 more times. You could copy-paste Entity-A’s cells (which copies validation), but then you’d overwrite the data. You could manually re-create every rule sheet by sheet — Format Cells → Data Validation → Settings → set Type → set Source → OK, 50 times per sheet. That’s over 500 clicks for your 11 sheets. The deadline is Friday.

This macro copies the validation rules and nothing else. Your data stays put. The dropdowns just appear.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A template sheet with data validation rules already set up on the range you want to copy
Destination sheets with the same structure — data in the same rows and columns as the template

Limitations:

The macro copies rules to the exact same cell positions on destination sheets. If Entity-B has headers in row 2 instead of row 1, the cell references will be off by one row.
Skips protected sheets automatically — unprotect them first if you want validation applied there
Input messages and error alerts are copied, but the macro can’t replicate custom validation formulas that reference sheet-specific ranges (e.g., =INDIRECT("Entity-A!Rates")). Formulas referencing the template sheet are adjusted to reference the destination sheet.
Only copies validation rules, not cell formatting, conditional formatting, or comments

#The Macro

Option Explicit

Sub CopyDataValidation()
    ' ── Data Validation Copier ──────────────────────────
    ' Copies data validation rules from a template range
    ' to the exact same cell positions on any number of
    ' destination sheets — without touching data.
    '
    ' Supports: dropdown lists, whole/decimal number
    ' limits, date restrictions, text-length limits, and
    ' custom formulas. Input messages, error alerts, and
    ' dropdown arrow preferences are preserved.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim srcRng As Range, cell As Range
    Dim dstWs As Worksheet
    Dim dstSheets As String
    Dim sheetNames() As String
    Dim i As Long, s As Long
    Dim sheetCount As Long, cellTotal As Long
    Dim skipped As Long, valCount As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Select the template range ──────────────────
    On Error Resume Next
    Set srcRng = Application.InputBox( _
        "Select the range that HAS the validation rules " & _
        "you want to copy (mouse-drag to select).", _
        "Template Range", Type:=8)
    On Error GoTo CleanUp

    If srcRng Is Nothing Then
        MsgBox "No range selected. Cancelled.", vbExclamation
        GoTo CleanUp
    End If

    ' ── Step 2: Count cells with validation ────────────────
    valCount = 0
    For Each cell In srcRng
        On Error Resume Next
        If cell.Validation.Type >= 0 Then valCount = valCount + 1
        On Error GoTo CleanUp
    Next cell

    If valCount = 0 Then
        MsgBox "No data validation rules found in the " & _
               "selected range.", vbExclamation
        GoTo CleanUp
    End If

    ' ── Step 3: Choose destination sheets ──────────────────
    Dim wsList As String, ws As Worksheet
    wsList = "Available destination sheets:" & vbCrLf & vbCrLf
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> srcRng.Worksheet.Name Then
            wsList = wsList & "  • " & ws.Name & vbCrLf
        End If
    Next ws

    dstSheets = InputBox(wsList & vbCrLf & _
        "Enter sheet names, separated by commas:" & vbCrLf & _
        "  Example: Entity-B, Entity-C, Entity-D" & vbCrLf & _
        "  Or type ALL for every sheet except the template.", _
        "Destination Sheets")

    If dstSheets = "" Then
        MsgBox "No sheets specified. Cancelled.", vbExclamation
        GoTo CleanUp
    End If

    If UCase(Trim(dstSheets)) = "ALL" Then
        s = -1 ' Flag: process all non-template sheets
    Else
        sheetNames = Split(dstSheets, ",")
    End If

    ' ── Step 4: Confirm before acting ──────────────────────
    Dim destCount As Long
    If s = -1 Then
        destCount = ThisWorkbook.Worksheets.Count - 1
    Else
        destCount = UBound(sheetNames) - LBound(sheetNames) + 1
    End If

    If MsgBox("Copy " & valCount & " validation rule(s) from '" & _
              srcRng.Worksheet.Name & "' to " & destCount & _
              " sheet(s)?", vbYesNo + vbQuestion, _
              "Confirm Copy") = vbNo Then GoTo CleanUp

    ' ── Step 5: Copy validation to each destination ────────
    Dim rowOff As Long, colOff As Long
    Dim dstCell As Range
    Dim found As Boolean
    Dim valType As Long

    sheetCount = 0: cellTotal = 0: skipped = 0

    For Each ws In ThisWorkbook.Worksheets
        ' Skip the template sheet
        If ws.Name = srcRng.Worksheet.Name Then GoTo NextSheet

        ' If not ALL, check if this sheet was requested
        If s <> -1 Then
            found = False
            For i = LBound(sheetNames) To UBound(sheetNames)
                If Trim(sheetNames(i)) = ws.Name Then
                    found = True
                    Exit For
                End If
            Next i
            If Not found Then GoTo NextSheet
        End If

        ' Skip protected sheets
        If ws.ProtectContents Then
            skipped = skipped + 1
            GoTo NextSheet
        End If

        ' Copy each validation rule
        Dim cellDone As Long
        cellDone = 0

        For Each cell In srcRng
            On Error Resume Next
            valType = cell.Validation.Type
            On Error GoTo CleanUp

            If valType >= 0 Then
                rowOff = cell.Row - srcRng.Row
                colOff = cell.Column - srcRng.Column
                Set dstCell = ws.Cells( _
                    srcRng.Row + rowOff, _
                    srcRng.Column + colOff)

                ' Clear existing validation on destination
                On Error Resume Next
                dstCell.Validation.Delete
                On Error GoTo CleanUp

                ' Copy based on validation type
                Select Case valType
                    Case 3 ' xlValidateList
                        dstCell.Validation.Add Type:=3, _
                            Formula1:=cell.Validation.Formula1
                    Case 1, 2, 4, 6, 7 ' Numeric, Date, Time, TextLength
                        dstCell.Validation.Add _
                            Type:=valType, _
                            AlertStyle:=cell.Validation.AlertStyle, _
                            Operator:=cell.Validation.Operator, _
                            Formula1:=cell.Validation.Formula1, _
                            Formula2:=cell.Validation.Formula2
                    Case Else ' Custom formula
                        dstCell.Validation.Add Type:=valType, _
                            Formula1:=cell.Validation.Formula1
                End Select

                ' Copy messaging properties
                With dstCell.Validation
                    .IgnoreBlank = cell.Validation.IgnoreBlank
                    .InCellDropdown = cell.Validation.InCellDropdown
                    .InputTitle = cell.Validation.InputTitle
                    .InputMessage = cell.Validation.InputMessage
                    .ErrorTitle = cell.Validation.ErrorTitle
                    .ErrorMessage = cell.Validation.ErrorMessage
                    .ShowInput = cell.Validation.ShowInput
                    .ShowError = cell.Validation.ShowError
                End With

                cellDone = cellDone + 1
            End If
        Next cell

        If cellDone > 0 Then
            sheetCount = sheetCount + 1
            cellTotal = cellTotal + cellDone
        End If

NextSheet:
    Next ws

    ' ── Step 6: Summary ────────────────────────────────────
    Dim msg As String
    If sheetCount = 0 Then
        msg = "No validation was copied." & vbCrLf & vbCrLf & _
              "All non-template sheets are either protected " & _
              "or were not selected."
    Else
        msg = "Copied " & cellTotal & " validation rule(s) to " & _
              sheetCount & " sheet(s)."
        If skipped > 0 Then
            msg = msg & vbCrLf & vbCrLf & skipped & _
                  " sheet(s) skipped (protected). Unprotect " & _
                  "and run again to include them."
        End If
    End If

    MsgBox msg, vbInformation, "Done"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#User selects the template range with the mouse

Set srcRng = Application.InputBox( _
    "Select the range that HAS the validation rules...", Type:=8)

The Type:=8 argument turns the InputBox into a range selector. The user drags to highlight cells directly on the worksheet instead of typing cell references. No guessing column letters, no typos — just click and drag.

The range is captured as a Range object so the macro knows exactly which cells to scan for rules and where those cells sit on the sheet. The row/column offsets computed later (cell.Row - srcRng.Row) let it map each rule to the same position on destination sheets.

#Sheet names are typed, not selected from a dialog

VBA doesn’t have a built-in multi-select sheet picker without building a UserForm. To keep the macro short and pasteable, we show the user a list of every sheet (except the template) and ask them to type names separated by commas. Typing ALL processes every non-template sheet at once.

wsList = "Available destination sheets:" & vbCrLf & vbCrLf
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> srcRng.Worksheet.Name Then
        wsList = wsList & "  • " & ws.Name & vbCrLf
    End If
Next ws

The list is built into the InputBox text so the user doesn’t need to guess or remember tab names. If a sheet name has a comma in it (rare in tax workpapers but possible), we note this as a known limitation.

#Validation type determines which properties to copy

Select Case valType
    Case 3 ' xlValidateList
        dstCell.Validation.Add Type:=3, _
            Formula1:=cell.Validation.Formula1
    Case 1, 2, 4, 6, 7 ' Numeric, Date, Time, TextLength
        dstCell.Validation.Add Type:=valType, _
            AlertStyle:=cell.Validation.AlertStyle, _
            Operator:=cell.Validation.Operator, _
            Formula1:=cell.Validation.Formula1, _
            Formula2:=cell.Validation.Formula2

Not all validation types use all parameters. Dropdown lists (xlValidateList, type 3) only need a source — no operator, no Formula2. Numeric and date validations need the operator (between, greater than, equal to, etc.) and both Formula1 and Formula2 for “between” ranges. Trying to pass an operator to a list validation throws a runtime error, so the Select Case routes each type to the correct .Add call.

The messaging properties (InputTitle, InputMessage, ErrorTitle, ErrorMessage) are independent of the validation type and always copied. So are the display preferences (ShowInput, ShowError, InCellDropdown, IgnoreBlank).

#Protected sheets are skipped with a message

If ws.ProtectContents Then
    skipped = skipped + 1
    GoTo NextSheet
End If

Protected sheets block programmatic changes to their validation rules. Rather than failing mid-operation, the macro skips them and reports how many were skipped at the end. The user can unprotect and run again to include them.

#The confirmation message box shows what will happen before acting

If MsgBox("Copy " & valCount & " validation rule(s) from '" & _
          srcRng.Worksheet.Name & "' to " & destCount & _
          " sheet(s)?", vbYesNo + vbQuestion, _
          "Confirm Copy") = vbNo Then GoTo CleanUp

The macro counts validation rules in the template range before doing anything destructive and shows the user exactly what it found. If the count looks wrong — say, the user expected 50 rules but the macro found only 2 — they can cancel and check the template sheet before committing.

#Why you should never copy-paste validation by copying cells

The instinct is to copy the Entity-A range, go to Entity-B, and Paste Special → Validation. This works for a single sheet but has two fatal flaws:

It also pastes data. If Entity-B has different numbers than Entity-A (different account balances, dates, text), Paste Special → Validation still copies the cell values unless you are very careful about selecting only Validation in the Paste Special dialog. One misclick and you’ve overwritten client data.
It doesn’t scale. 12 sheets × 50 cells × 3 Paste Special dialogs = 1,800 manual operations. The macro replaces all of them with two InputBox responses.

The macro reads validation properties from the template and writes only validation to the destination — it never touches cell values.

#Adapt It

Copy from one workbook to another. Replace ThisWorkbook.Worksheets with a loop over workbooks in a folder, or let the user pick a template workbook with Application.GetOpenFilename(). This is useful when you have a “gold standard” template workbook and need to apply its validation to 30 client workbooks.

Skip certain sheets automatically. Add a filter before the destination loop to exclude sheets with certain names without typing them every time:

If ws.Name Like "Summary*" Or ws.Name = "Cover" _
   Or ws.Name Like "*Notes*" Then GoTo NextSheet

Copy validation to a different cell location. If the destination sheets have the same number of rows and columns but the data starts in a different place (e.g., template starts at B5, destination starts at C8), add offset constants:

Const ROW_SHIFT As Long = 3  ' destination is 3 rows down
Const COL_SHIFT As Long = 1  ' destination is 1 column right
' In the copy loop:
Set dstCell = ws.Cells( _
    srcRng.Row + rowOff + ROW_SHIFT, _
    srcRng.Column + colOff + COL_SHIFT)

Copy to only visible sheets. If some sheets are hidden for a reason (prior-year archives, lookup tables), add a visibility check to skip them:

If ws.Visible <> xlSheetVisible Then GoTo NextSheet

Add validation to every numeric cell. Instead of selecting a template range, loop through every cell on the destination sheet and apply a blanket rule — for example, requiring every numeric entry to be positive:

For Each cell In ws.UsedRange
    If IsNumeric(cell.Value) And Not cell.HasFormula Then
        cell.Validation.Add Type:=xlValidateDecimal, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlGreater, Formula1:="0"
    End If
Next cell

See also: Data Validation Stripper removes all validation rules from every sheet. Use the copier to apply clean rules after stripping stale ones. Sheet Lock & Unlock protects/unprotects every sheet after validation rules are in place.

Download the sample workbook (multi-entity consolidation with template sheet and 5 destination sheets)

xlsx 14 KB