Is there a way to manipulate each item in Data Validation in VBA?

Question

I am trying to do a loop function in VBA to select every item in the data validation (22 items) and Copy and Paste on newsheets based on the item name.

I tried Record Macro to see the language from selecting different data validation items but nothing is registering. Is there a way to manipulate each item in Data Validation in VBA?

Answer 1

There are 2 forms of DV. One that uses a list of cells like:

and the other that uses an internal comma separated list like:

This code will handle either form:

Sub IsDV()
    Dim s As String, r As Range, rng As Range

    s = "NO DV"
    On Error Resume Next
    With ActiveCell
        s = .Validation.Formula1
        On Error GoTo 0
    End With

    If s = "NO DV" Then
        MsgBox s
        Exit Sub
    End If

    If Left(s, 1) = "=" Then
        Set rng = Range(Mid(s, 2))
        For Each r In rng
            MsgBox r.Value
        Next r
        Exit Sub
    End If

    ary = Split(s, ",")
    For Each a In ary
        MsgBox a
    Next a

End Sub

EDIT#1:

As the picture shows, Formula1 creates a string. If that string begins with an = sign, then the rest of the string is an Address. So I discard the = sign and make a range. Knowing the range allows me to grab the items