Save specific worksheet with name from range to optional location


Hi, using the tutorial on how to save a worksheet as a new file and some tweaking I can save a specific worksheet with as new name a value in a cell.  But of course I want more. I want the user to get a choice as to where it wants to save the file rather than it being "hard coded' in the macro. I have found macros that give the user the choice but then they have to type in the name which I don't want (long name with dates and what have you).

this is what I have to save the worksheet with name from cell:

Sub Save_Specific_Worksheet_as_New_File()

    'The name of the sheet that will be copied to a new file

    SheetToCopy = "Data"

       'The name of the new file that will be created 

   NewFileName = Range("Data!A1")

    'Puts the worksheet into its own file (workbook)


   'Saves the new Excel workbook

    ActiveWorkbook.SaveAs "D:\puppy\" & NewFileName & ".xlsx"

    'Closes the newly created workbook so you are still looking at the original workbook


End Sub




There are four steps to the solution you need.

  1. Make the rules for how to create a file name, perhaps like,
    TXL 2010207 Daily Report
    where TXL is an organizer, the date, and a subject.
    The orginzer might be fixed for all or selected from a particular cell in the workbook itself or determined relative to such a cell.
    The date might be the current date or one taken from the workbook's current name. Perhaps you need the time, too.
    The subject might be the same for all workbooks or it might be taken from the workbook in some way.
    Having made the rules get the code to create the name for each workbook. That would be a function, not  sub.
  2. Google for "Folderpicker". You will find code to call up Office's own dialog box allowing you to select a folder in which to save your workbook. The code is short and simple and you would be allowed to define a default destination that is hard-coded.
  3. Put the two componenets together, completing the saving action when the suer presses the OK on the dialog box.
  4. Design a method to call the macro. You may have to place it in a macro library that loads whenever Excel is loaded. You might then assign a keyboard shortcut to call the macro or call it from the Macro button on the Developer tab.

As you see, the code you have doesn't fit into the project. You might still try to fit your project into the code you have. But if that is your intention you have a lot of designing to do first.


Answer the Question

You must create an account to use the forum. Create an Account or Login