Is there a macro or anyway I can get data validation lists into a sheet?

[tonywatsonhelp]

Hi Everyone,
I've inherated a document and one page is loads of dropdown quetions,

now the person has set up the data validation for evry one by typing into the data validation box the names,
is there any way I can export this into a sheet?

so for example J7 = Tom Jones,Chris Prat,Courtney Cox,Wayne Ker
any way i can pull this out into a sheet ?
thanks
Tony

 

[earthworm]

No. if the list is hardcoded into validation list separated by commas . However if the list is being selected from range then you can

 

[Akuini]

Try:

Range("K7") = Range("J7").Validation.Formula1

 

[tonywatsonhelp]

Akuini that worked perfectly,
now to really save me time
can we do this to an entire document?

 

[Akuini]

1. In what ranges are the data validation located?
2. Do you want to return each data validation list to the next cell (as my code does)?

 

[Peter_SSs]

can we do this to an entire document?

Perhaps like this (only lists results for cells with Data Validation type is Allow: List)

Sub DetDV()
  Dim ws As Worksheet
  Dim a As Variant
  Dim i As Long, k As Long
  Dim c As Range, DVCells As Range
  Dim wsName As String
  
  Worksheets.Add(Before:=Sheets(1)).Name = "DV Lists"
  ReDim a(1 To Rows.Count, 1 To 3)
  For i = 2 To Sheets.Count
    With Sheets(i)
      Set DVCells = Nothing
      On Error Resume Next
      Set DVCells = .Cells.SpecialCells(xlCellTypeAllValidation)
      On Error GoTo 0
      If Not DVCells Is Nothing Then
        wsName = .Name
        For Each c In DVCells
          If c.Validation.Type = 3 Then
            k = k + 1
            a(k, 1) = wsName: a(k, 2) = c.Address(0, 0): a(k, 3) = c.Validation.Formula1
          End If
        Next c
      End If
    End With
  Next i
  With Sheets(1).Range("A2:C2").Resize(k)
    .Rows(0).Value = Array("Sheet", "Cell", "DV List")
    .Value = a
    .EntireColumn.AutoFit
  End With
End Sub