The blank "codename" is a known issue, but only with newly inserted sheets
since the previous save, and whilst the VBE is closed (AFAIK). I'm surprised
this affects you as presumably you are trying to reference a sheet that has
been saved at least once.

In my example I named a large area. That was deliberate to reduce the
chances of user "removing" the range entirely, and hence the need to parse
the Refersto string for the sheetname if that has occurred. I see you are
now naming a just single cell.

Apostrophes get added if the sheet name has certain characters, such as
space, dash etc. So yes, you need to look for the possibility of these.

I don't follow the purpose of this line:

5) Application.Range(shtName).value 'get failed

Anyway, looks like you have an overall solution.

Regards,
Peter T


"Vinit" wrote in message
oups.com...
Hi Peter,
Thanks!
I find the reason for the blank sheet name. Without opening VBE if we
access CodeName it returns blank. If you open VBE(at this time Excel
initialize the CodeName) and then access the codename it returns
successfuly. It is strange but thats the only reason.

Currently I am using Name Range(i.e."ActiveWorkbook.Names.Add)method to
keep track of Chenge in cell location(insert/Delete) and also for the
worksheet Name. But an interesting problem with it is :

1) Add name range:
ActiveWorkbook.Names.Add "MyName", _
Worksheets("Sheet1").Range("A1")
ActiveWorkbook.Names("MyName").Visible = False

2)Re-Name the Worksheet to Sheet 1 2(include numbers & spaces in
Name)

3)Get the Value of name range:
shtName = Range("MyName").Parent.Name ' Return Value'Sheet 1 2'!A1

4) In this case the sheetname will be 'Sheet 1 2' or in some cases
Sheet'1 2' (Excel adds Single quote(') on its own to sheetname)

5) Application.Range(shtName).value get failed

6)U need to trim the single qupte from ShtName which excel adds if
there is space and numbers in sheetname

Regards,
Vinit