Save and close

[kiss]

Could someone please help with the following:

I have a master excel worksheet (Spare Parts requisition.xls)that consists of only one sheet.
I would like to save a copy of this sheet, retaining this worksheet as the master copy but creating a new file from it after the user has added the data.
I want to use a button to do this.
I have created a cell that holds an equation to create a file name. (cell - V5).
Cell equation: =CONCATENATE(N3," ",D5," ",O5)
I want to use this cell data for the file name.
I want to store this file in a folder C:\Documents and Settings\tdallow\Desktop\MMAU Service\Forms\Completed Parts Requisitions.
After saving the form I would like the master to close.
So after data has been entered and the form saved there should still be a copy of the original master and a copy of the newly created form.

I am using 2003 excel.
Would I have to make the "Master" a template or can I keep it as a form.

Thank you for your assistance.
kind regards

 

[Datsmart]

This code will create a new workbook with a copy of your active worksheet as the only sheet. It will then Name, Save and Close the new workbook.

Sub CandP()
Dim TargetWB As String
Dim ThisPath As String
    'Assign Variables
    TargetPath = "C:\Documents and Settings\tdallow\Desktop\MMAU Service\Forms\Completed Parts Requisitions"
    TargetWB = Range("V5").Value
    ThisFile = TargetPath & "\" & TargetWB & ".xls"
    'Create new workbook with copy of active worksheet
    ActiveSheet.Copy
    'Name, Save and Close new workbook
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=ThisFile
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

Code assumes you have a valid WorkBook name in cell "V5" without the file extension. Adjust code as needed.

 

[kiss]

Thanks Datsmart, fantastic, just what I was looking for and worked first time. Really appreciate your prompt response.