Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saving file name based on value in predetermined cell | Excel Discussion (Misc queries) | |||
How do I copy a date in a worksheet cell to another worksheet? | Excel Worksheet Functions | |||
Worksheet Revision Date only once that day | Excel Discussion (Misc queries) | |||
Automatic Populate Todays Date in Cell when File is Saved. | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |