Hi Mike,

Thanks for your reply. I have the same code. But it still fails to
work without opening the code and refresh.

Heres my code:

Sub WsNames()

Dim i As Long
Dim Sh As Worksheet
i = 6


For Each Sh In ActiveWorkbook.Sheets

Range("B" & i) = Sh.Name
Range("A" & i) = Sh.CodeName
i = i + 1

Next

End Sub

Any idea to rectify?

Thanks

DC