Is this to be done from the previous month's statement file itself?

Where do you intend to get the date from: the system, or a cell on a sheet?

And is that mm.dd.yy or dd.mm.yy for part of the filename?

I will presume that you want to save it with new filename when date in a
cell on a particular sheet is changed.

This code would go into the Worksheet_Change() event processor. To put it
there, right-click on the sheet's name tab and choose [View Code] from the
list. Cut and paste this code into the module opened up in the VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theDay As String
Dim theMonth As String
Dim theYear As String
Dim newFileName As String
'change this address to the address of the
'cell with the date to be used in it
'be sure to use absolute addressing
'using the $symbols before the column ID
'and row number.
If Target.Address < "$A$1" Then
Exit Sub ' no date change
End If
If Not IsDate(Target) Then
Exit Sub ' invalid as a date
End If
theDay = Trim(Str(Day(Target)))
If Len(theDay) = 1 Then
theDay = "0" & theDay
End If

theMonth = Trim(Str(Month(Target)))
If Len(theMonth) = 1 Then
theMonth = "0" & theDay
End If

theYear = Trim(Str(Year(Target)))
If Len(theYear) = 1 Then
theYear = "0" & theYear
Else
theYear = Right(theYear, 2)
End If
If InStr(ThisWorkbook.FullName, " ") = 0 Then
'you need to save it initially with
'proper name
MsgBox "Cannot auto-save at this time. Save the file with correct
name now.", vbOKOnly, "Name Problem"
Exit Sub
End If
'this presumes that the last space
'in the filename is the space just
'before the date portion of the filename
newFileName = Left(ThisWorkbook.FullName, _
InStrRev(ThisWorkbook.FullName, " ")) & _
theDay & "." & theMonth & "." & theYear _
& ".xls"
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=newFileName
Application.DisplayAlerts = True
End Sub

I can envision one or two things going wrong, one we catch: the workbook has
never been saved before. The other is if the filenaming convention you use
is not as shown. The code depends on there being at least a single space
between the customer name portion and the date portion, and no more spaces in
the name after that. If you adhere to that convention, it should work
reliably for you.

You need to change the address of the cell with the date in it, and if you
want a different sequence for the day.month portion of the filename, just
change the sequence they are built up in the newFilename= statement.


"Joe" wrote:

Hi! How can u create a Macro that will save your client previous month
statement ie 'Tesco 01.04.07' with the a new date ie Tesco 01.05.07