Saving as new spreadsheet

[dpaton05]

I am trying to create code to save my workbook, then save it again as a string entered in a textbox. I then want it to close the new workbook and reopen the original one. The workbook name can change so I can't use the name of the workbook as a reference point. I have this code so far:

Private Sub cmdNewTool_Click()
    With ActiveWorkbook
        .Save
        .SaveAs txtNewDoc.Text & ".xlsm"
        .Close
    End With
    Workbooks.Open Application.RecentFiles(1)
End Sub

This code saves it, saves it again as the new workbook, closes it, then reopens excel but doesn't open the last workbook that I saved the new workbook from. Can someone help me get the original workbook to open please?

 

[Michael M]

Why not simply

Private Sub cmdNewTool_Click()
ActiveWorkbook.SaveCopyAs Filename:="C:\Temp\" & txtNewDoc.Text & ".xlsm" 'change filepath to suit
End Sub

 

[Michael M]

or in the current directory

Private Sub cmdNewTool_Click()
ActiveWorkbook.SaveCopyAs Filename:=txtNewDoc.Text & ".xlsm"
End Sub

 

[dpaton05]

Why not simply

Private Sub cmdNewTool_Click()
ActiveWorkbook.SaveCopyAs Filename:="C:\Temp\" & txtNewDoc.Text & ".xlsm" 'change filepath to suit
End Sub

Does the save copy as leave the current workbook open?

 

[dpaton05]

That doesn't appear to be saving any additional copies.

 

[Michael M]

Yes !
What is in Textnewdoc ??

 

[dpaton05]

Yes it is, my appologies, the files didn't appear but I tried again and then it did.

 

[dpaton05]

Yes !
What is in Textnewdoc ??

The user enters the name of the new document.

 

[dpaton05]

How would I go about making some changes to the new document? Such as there are sheets that need the year incrementing in the name of the sheet and there are cells that contain the year that need incrementing too.

Thanks Michael

 

[Michael M]

Such as there are sheets that need the year incrementing in the name of the sheet and there are cells that contain the year that need incrementing too.

I thought that had been well covered in your previous threads !