return ws name formula not working after moving ws to new wb

[Reddog94]

I am using the following formula to display the first 7 characters of a worksheet name in a cell.

=LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256),7)

It works perfectly. However, when I copy the worksheet, move it it a new workbook and save the workbook with a name that is identical to the worksheet name, the formula now returns #VALUE.

Anyone know why the formula no longer works after the copy/move/save?

 

[Michael M]

1. How are you copying the formula, by highlighting in the formula bar ?
2. Does the new wb have a filename longer than 7 characters ?

 

[Nalani]

See if this helps:

=LEFT(REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""),7)

 

[Nalani]

Never mind:

Just tried it and same problem. Still trying to figure it out.

 

[Nalani]

It works perfectly. However, when I copy the worksheet, move it it a new workbook and save the workbook with a name that is identical to the worksheet name, the formula now returns #VALUE.

This is what I found out with a few tests:

1. If you are R/Clicking the Tab > Move or Copy > Create Copy >(new book)
2. Then saving the (new book) as ANY name, you will get the error.

If you create the new book first, name it, then Copy the sheet over, the formula will work.

Don't know why, but that's what happened with my testing.

 

[Reddog94]

Well rats - the copy/move/save is done in with code, which says to copy the ws, move it to a new book, then name the new book with the name of the ws. My vba is too limited to know how to create the new book and name it the ws name before copying the ws over - but I'll keep working on it...

Thanks all.

 

[Michael M]

Why don't you post the code, it might help us, help you solve the problem

 

[Reddog94]

Michael - I should have done that to begin with, sorry (I'm new and still learning). However, I fixed this problem by doing a copy/paste value in the cell in question before copying it to the new workbook. I only needed the formula to populate the data upon creation, after that it's fine to hard code it. Thanks for all your help!

 

[Michael M]

Ok, glad you got it sorted!