Saving worksheet in new file with date AND cell value as file name
 

I've had success with a macro that moves a specific sheet to a new
workbook and names that file with the contents of a particular cell. I
have only two small problems.

1. I need to add the date to the file name so that if the particular
cell that names the file is "Acme", then the new file would be named
"Acme 5-26-06"

2. With my current code, the new file is saved just fine, but it still
leaves an additional book open. In other words, I have the Master
Workbook from which the code will run and create "Book 2". From that,
the code Saves As "Book 2" with the name from that specific cell. It
automatically closes the new book, but leaves "Book 2" open. Is there
any way to make that close automatically?

Thanks to all.

Saving worksheet in new file with date AND cell value as file name
 

Silly me, I forgot to put the code I already have.
Here goes:
ub copytofile()
'
' copytofile Macro
' Macro recorded 5/26/2006 by Michael Berrier
'

'
Sheets("MANIFEST").Select
Sheets("MANIFEST").Copy
Worksheets("MANIFEST").Copy 'to a new workbook
'new workbook is now active
With ActiveWorkbook
.SaveAs Filename:=.Worksheets("MANIFEST").Range("C13").Val ue
.Close savechanges:=False 'if you're done with it.
End With
End Sub

Saving worksheet in new file with date AND cell value as file name
 

To solve number 1 you can use

ActiveSheet.Name = "Acme " & Application.Substitute(Date, "/", "-")

The substitute function is a devise to remove the slash characters
which are illegal in sheet names.

For the second you can use

Thisworkbook.close

You may need to precede this with

Thisworkbook.saved = True

to avoid the dialog box asking if you want to save changes.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=545980

Saving worksheet in new file with date AND cell value as file name
 

That fixed my naming problem, but the other problem persists.

When the macro runs, I want to close, disregard or skip altogether the
"Book2" file. Using Thisworkbook.close tries to close the Master file,
which I want to keep open.

Here's the code I have now:
Sub copytofile()
'
' copytofile Macro
' Macro recorded 5/26/2006 by Michael Berrier
'

'
Sheets("MANIFEST").Select
Sheets("MANIFEST").Copy
Worksheets("MANIFEST").Copy 'to a new workbook
'new workbook is now active
With ActiveWorkbook
.SaveAs Filename:=.Worksheets("MANIFEST").Range("C13").Val ue &
Application.Substitute(Date, "/", "-")
.Close savechanges:=True 'if you're done with it.
ThisWorkbook.Close
End With
End Sub

Thanks.

Saving worksheet in new file with date AND cell value as file name
 

Ok, last thing....How do I change the directory the file is saved to?