naming fields

[Christian Galbavy]

Hello!

Excel does some crazy things, when I try to name fields.
In my code I copy a sheet, rename it, and then I name the fields. When I
start the macro, the sheet gets inserted, but the fields do not get named.
The crazy thing is, that when I step through the code with the debugger,
there is no problem, all fields get their name!
**********
Sheets("first_sheet").Copy Before:=Sheets("third_sheet")
Sheets("first_sheet (2)").Name = "second_sheet"
Application.CutCopyMode = False
ThisWorkbook.Names.Add Name:="my_number", RefersToR1C1:="=second_sheet!R1C1"
**********

What is the reason for this crazy behaviour? Hope somebody can help me.

Regars
Christian Galbavy

 

[Tom Ogilvy]

Sub B()
Sheets(1).Name = "first_Sheet"
Sheets(2).Name = "third_sheet"
Sheets("first_sheet").Copy Before:=Sheets("third_sheet")
Sheets("first_sheet (2)").Name = "second_sheet"
Application.CutCopyMode = False
ThisWorkbook.Names.Add Name:="my_number", _
RefersToR1C1:="=second_sheet!R1C1"
MsgBox ThisWorkbook.Names("my_Number"). _
RefersToRange.Address(external:=True)
End Sub

in a general module worked fine for me.