I need to use a date as part of my .xls filename.

Greetings all, thank you for your help in advance!

I have a macro that creates a separate .xls file for each of my worksheets.
That macro uses cell B1, the worksheet name, and cell B13 to create the file's name.
For example if in B1 the value was 1000 and the worksheet was named Charles and B13 has the value of GL the filename would be 1000_Charles_GL.xls

My problem is that if either B1 or B13 is in the DATE format, I get Run-time error 1004.
If you want to see it for yourself, I did a screen grab:
http://dclicc.squarespace.com/storag...ments/1004.bmp

I think the problem is that windows (I have XP) will not allow a file to be created with "/" marks, and excel wants to use them? I have tried changing the format to dates with dashes, but it doesn't help. If I change the catergory/format to general it will work, but of course that would give me a useless number. I DO NEED the date in the file name.

I posted the macro below if that will help.

Again, thanks!
- Skot

Please Login or Register  to view this content.

Hello Skot,

Welcome to the Forum!

This will replace the forward slash ( "/") characters with underscores ("_") in the date.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Leith,

Your code did allow the value of B13 (a date) to go into the file name, so I see it can be done. WOO HOO!

HOWEVER, now, only the value of B1 and B13 of the ACTIVE WORKSHEET is now being used. Which means for every .xls file created the parts from B1 and B13 are the same.

My directory looks like this:

1590_John_1_16_2011.xls
1590_Carol_1_16_2011.xls
1590_Paul_1_16_2011.xls
1590_Mark_1_16_2011.xls
1590_Leon_1_16_2011.xls
etc...

When it should look like this...

1590_John_1_16_2011.xls
1631_Carol_1_12_2011.xls
2159_Paul_1_10_2011.xls
0090_Mark_1_13_2011.xls
1430_Leon_1_14_2011.xls

Again, thank you for your time, it is clear you are very talented!
-Skot

SNB,

This did not work. I defined sh as a worksheet, but still Excel had problems with the .SaveAs line. "Compile error Syntax Error"
THANK YOU for your help.

- Skot

Skot, please don't quote whole replies unless necessary.

Your problem with Leith's code had been solved by mine: cfr the dots before .
Range()
You could have removed the superfluous comma in the SaveAs-line yourself.

Please Login or Register  to view this content.

Okay, now Excel is just messing with me.
In my test file (named test2.xls - very original eh?) I was able to make the following code work PERFECTLY!

Please Login or Register  to view this content.

I was happy, I was doing the dance of joy, LIFE WAS GOOD!

Then I tried my macro in a "read-world" file and I get

EDIT: If you want to see the error for yourself:
http://dclicc.squarespace.com/storag.../1004_copy.bmp

Run-Time error '1004':
Method 'Copy' of object'_worksheet' failed
I click on Debug and the line
w.Copy
is in yellow!!!!!!

The difference between my test file and my real world file was minimal.
I though maybe the real world file had too many tabs, but I tried again and again, with fewer and fewer worksheets and that wasn't it.


What did I do wrong?
Thanks again.
-Skot


ANSWER: I had a hidden worksheet. Once that was visible, it works all day long! THANK YOU ALL!

PS - How do you guys keep your sanity?