Bom dia!!

Veja se ajuda.
Fonte:
http://msmvps.com/blogs/nateoliver/default.aspx

Sub AleVBA_18780()
Dim nm As Name, n As Long, y As Range, z As Worksheet
Application.ScreenUpdating = False
Set z = ActiveSheet
n = 2
With z
    .[a1:g65536].ClearContents
    .[a1:D1] = [{"Nome","Guia","Intervalo Inc","Intervalo Final"}]
    For Each nm In ActiveWorkbook.Names
        .Cells(n, 1) = nm.Name
        .Cells(n, 2) = Range(nm).Parent.Name
        .Cells(n, 3) = nm.RefersToRange.Address(False, False)
        n = n + 1
    Next nm
End With

Set y = z.Range("c2:c" & z.[c65536].End(xlUp).Row)
y.TextToColumns Destination:=z.[C2], DataType:=xlDelimited, _
    OtherChar:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1))
[a:d].EntireColumn.AutoFit

Application.ScreenUpdating = True
End Sub

Att

Olá luizhcosta,

Vá na guia 'Fórmulas ' >> 'Usar em Fórmula >> 'Colar Nomes' >> 'Colar Lista'
(Execel 2010-2013)

Valeu Rapaz!

Obrigado.