Be REALLY grateful for some quick advice with this one.....

[Audiojoe]

If I want to set a macro to save a file as a certain date, how do I do it?

At the moment, the macro copies a block of data, pastes it into a template, then I want it to save that template as last week's date. For instance, the file template is called Weekly Report Template, when the macro saves it I need it be saved as:

Weekly Report - 18th-24th February 2002

as that is the period that the report covers. However, of course NEXT week it will have to save it as:

Weekly Report - 25th Feb-3rd March 2002

and so on....

Can anyone help me?

 

[pilot]

On 2002-02-26 06:10, Audiojoe wrote:

If I want to set a macro to save a file as a certain date, how do I do it?

At the moment, the macro copies a block of data, pastes it into a template, then I want it to save that template as last week's date. For instance, the file template is called Weekly Report Template, when the macro saves it I need it be saved as:

Weekly Report - 18th-24th February 2002

as that is the period that the report covers. However, of course NEXT week it will have to save it as:

Weekly Report - 25th Feb-3rd March 2002

and so on....

Can anyone help me?

What is the relationship between the date you run the macro and the dates of the material being saved? For example, for
Weekly Report - 18th-24th February 2002
will the save take place on Feb 25th? Feb 26th? Some varied date but always during M-F after the week being saved?

 

[Juan Pablo González]

Well, try this.

Put this UDF somewhere in the module.

Function LastWeek(Dt As Date) As String
Dim T1 As Long
Dim T2 As Long
T1 = Dt - (WeekDay(Dt, vbMonday) - 1)
T2 = T1 + 6

LastWeek = Format(T1, IIF(Month(T1)<> Month(T2),"dd mmm","dd")) & "-" & Format(T2, "dd mmmm yyyy")
End Function

This function accepts a DATE as a parameter, and returns the WEEK of that date (Mon - Sun) trying to resemble your format.

Then, you could use it like this:

ActiveWorkbook.SaveAs "Weekly Report " & LastWeek(Date-7) & ".xls"

I think that covers it.

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-02-26 06:36

 

[Audiojoe]

It will always be on the day after the week has finished, for example:

18th-24th Feb 2002

would have been saved on the 25th

 

[Juan Pablo González]

On 2002-02-26 06:36, Audiojoe wrote:
It will always be on the day after the week has finished, for example:

18th-24th Feb 2002

would have been saved on the 25th

Look at my reply, I think that should solve your problem.

 

[Audiojoe]

Thanks for getting back to me so quickly, I'll get it tried out

Regards

 

[Audiojoe]

OK, I'm getting a little lost now, what is wrong with this? I guess it's glaring but I'm new to this racket...


Sub savingcopiedscripts()

' savingcopiedscripts Macro
' Macro recorded 26/02/02 by aroche

Function Lastweek(Dt As Date) As String
Dim t1 As Long
Dim t2 As Long
t1 = Dt - (WeekDay(Dt, vbMonday) - 1)
t2 = t1 + 6

Last week = Format(t1, IIf(Month(t1) <> Month(t2), "dd mmm", "dd")) & "-"

ChDir "H:Stats"
ActiveWorkbook.SaveAs FileName:="H:Stats", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

End Function

 

[Audiojoe]

Hi, does anyone know how on earth I can fix the above?

 

[Audiojoe]

Anyone?

 

[Juan Pablo González]

Do you really need to post every 3 seconds if no one has answered ?

The problem is that you're using a function inside of a Sub. Put it like this:

Function LastWeek....
....
End Function

Sub YourSub()

.....

End Sub