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

[michaelberrier]

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.

 

[michaelberrier]

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").Value
.Close savechanges:=False 'if you're done with it.
End With
End Sub

 

[mrice]

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.

 

[michaelberrier]

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").Value &
Application.Substitute(Date, "/", "-")
.Close savechanges:=True 'if you're done with it.
ThisWorkbook.Close
End With
End Sub

Thanks.

 

[michaelberrier]

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