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

Audiojoe

Active Member
Joined
Feb 20, 2002
Messages
285
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?

:confused:
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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?

:confused:

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?
 
Upvote 0
Well, try this.

Put this UDF somewhere in the module.

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top