Opening and excel file using VBA

Dear Friends,

I have a spread sheet that won't do as I want it too. I do hope someone reading this can help me, the dead line is fast approaching.

What it is, a userform being used as a password input box to reset the spreadsheet ready for the next year. What I need it to do is to save the workbook as it is, then 'save as' a different name, then reopen the original spreadsheet. Most works, it just wont reopen the original spreadsheet. I think I have tried pretty much everything and I just cant make it work. It saves the spreadsheet, saves as, closes the workbook (keeps excel open) but then thats it.

The code as it stands is as follows.

Private Sub continueres_Click()
If Passwordyear.password.Text = "Password1" Then
Passwordyear.password.Text = ""
Passwordyear.Hide

ActiveWorkbook.Save

Dim dt As String, wbNam As String

wbNam = "MMS_"
dt = Format(CStr(Now), "dd_mm_yyyy")
ActiveWorkbook.SaveAs FileName:=wbNam & dt

ThisWorkbook.Close

Application.Workbooks.Open ("Maintenance Management System (Draft - MPR) - Copy (Autosaved).xlsm")

Else
MsgBox "Incorect Password"
Passwordyear.Hide

End If


End Sub

Many thanks

Michael

Hi, m.rigby55,

welcome to Excel-Forum.

Before we will be able to help you should change the title of your thread (according to Rule #1) and add code-tags around your procedure (Rule #3).

And maybe have a look into the VBA help for SaveCopyAs.

Ciao,
Holger

Hi, Michael,

SaveCopyAs will only save a copy and stay in the original workbook (so you won´t have to close and reopen). Caveat is that the extension must be the very same as that of the original workbook.

Ciao,
Holger