+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    michaelberrier
    Guest

    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. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Response

    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.
    Martin

  3. #3
    michaelberrier
    Guest

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


  4. #4
    michaelberrier
    Guest

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

    Thanks.


  5. #5
    michaelberrier
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1