Save As Macro

  • Thread starter Thread starter Ronbo
  • Start date Start date
R

Ronbo

I save files "name +date" such as. "dell may 03" date
always has the last six spaces . i.e. next month will
be "dell jun 03"

I am looking for a macro that after completing procedures
in a workbook would open the "Save As" dialog box and 1.
remove the previous date 2. save the file as "name + new
date" The update is always done in the month of the file
date.

Any help would be very much appreciated.
 
Try this.


Private Sub CommandButton1_Click()

tmpName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 10)

Select Case Month(Now())
Case 1
tmpMonth = "Jan "
Case 2
tmpMonth = "Feb "
Case 3
tmpMonth = "Mar "
End Select

tmpYear = Right(Year(Now()), 2)

ActiveWorkbook.SaveAs FileName:="C:\WINDOWS\Desktop\" _
& tmpName & tmpMonth & tmpYear, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub




Michael Tomasura
 
-----Original Message-----
Try this.


Private Sub CommandButton1_Click()

tmpName = Left(ActiveWorkbook.Name, Len (ActiveWorkbook.Name) - 10)

Select Case Month(Now())
Case 1
tmpMonth = "Jan "
Case 2
tmpMonth = "Feb "
Case 3
tmpMonth = "Mar "
End Select

tmpYear = Right(Year(Now()), 2)

ActiveWorkbook.SaveAs
Click to expand...
FileName:="C:\WINDOWS\Desktop\" _
& tmpName & tmpMonth & tmpYear, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub




Michael Tomasura



.
Thanks a lot for your help. It is getting me on the
Click to expand...
way. The trouble I am having at this point is that it is
putting the name in twice i.e. "delldell feb 04" and two
it is putting it into the wrong (parent) directory and I
have changed your desktop dir to the correct one.

Again. thanks a lot for your help. If you can add any
further suggestions, they would be appreciated.
 
Back
Top