one way:

Public Sub ListConstants()
Dim wsSheet As Worksheet
Dim rCell As Range
Dim rDest As Range
Dim rConstants As Range
Dim nNumSheets As Long
Dim i As Long

nNumSheets = Sheets.Count
With Worksheets.Add(After:=Sheets(nNumSheets))
.Name = "Constants"
With .Range("A1:C1")
.Value = Array("Sheet", "Cell", "Value")
.Font.Bold = True
End With
Set rDest = .Range("A2")
End With
For i = 1 To nNumSheets
On Error Resume Next
Set rConstants = Worksheets(i).Cells.SpecialCells( _
xlCellTypeConstants)
On Error GoTo 0
If Not rConstants Is Nothing Then
For Each rCell In rConstants
With rCell
rDest.Value = .Parent.Name
rDest(1, 2).Value = .Address(False, False)
rDest(1, 3).Value = .Value
End With
Set rDest = rDest(2, 1)
Next rCell
End If
Next i
End Sub



In article ,
trickdos wrote:

Hey Guys,

I am interesting in writing a macro to cycle through all the sheets of
a workbook and see in each cell if it is a formula or if it is a
hardcode. If it is a hardcode, can it create a list on a seperate
sheet of the cell address.

I appreciate any help you can give me.