This code in the Open event of your master workbook will create a new copy of the master workbook with a name that includes a sequential number, that updates each time the master is opened. This example creates FormA0001.xls, FormA0002.xls etc.
In your master workbook, click Alt + f11 (The Alt key and function key 11).
In the visual basic window that opens, look in the Project explorer (usually on the left) - if not visible select View- Project explorer from the VB Menu. Find the name of your master workbook - after VBAProject e.g., VBAProject(Master.xls)
In the Microsoft Excel Object double click 'ThisWorkbook'.
In the code window on the right enter this code:
Private Sub Workbook_Open()
Dim intSequence As Integer
Dim strStem As String
Dim strType As String
'stem of filename
strStem = "FormA"
strType = Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - _
InStr(1, ActiveWorkbook.Name, ".") + 1)
'get last sequence number
intSequence = Worksheets("Sheet2").Range("A1").Value
'save next sequence number
Worksheets("Sheet2").Range("A1").Value = intSequence + 1
'Save this Master
'save as a new Workbook
Me.SaveAs Filename:=strStem & Format(intSequence, "000#") & strType
Note that the line of code starting strType has been split over two lines using the line continuation character '_'
It can be copied and pasted 'as is' or the '_' can be removed and brought back to a single line.
Save from the VB file menu
Use Alt+f11 to go back to the main Excel workbook.
Goto Sheet2 and enter the number 0 in cell A1.
Save the workbook and close it.
Open the workbook and it will update the number in cell A1 of Sheet2 and then save itself, then save As with a new filename - in this example FormA0001.xls
The new Workbook is saved in the default Workbook format and with the same extension as the Master.The user now has a unique named workbook and it is their responsibility to save it.
Hope this was what you were looking for, or at least gives you an idea for how to do what you wanted.