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)
ThisWorkbook.Sheets(SheetToCopy).Copy
'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
ActiveWorkbook.Close
End Sub