Autosaving an excel sheet as current date

[samchox]

Hi,

Im trying to create a template which, as soon as it is opened, gets saved [probably with auto_open macro] as mmm dd, in a specified folder.

Second part of this problem is, data from these saved sheets is collated in a workbook. Now the links in the workbook have to be dynamic so as to be able to link to sheets only if they are present.

For example, I open template on Feb 2, 4, 5 and 7. However the workbook where I have created links, will expect to see Feb 2, 3, 4, 5, 6 and 7 and will probably give me a REF/# because of the missing sheets.

Any advise?

 

[BrianB]

You can use the Open event to do this :-
'----------------------------------------------
Private Sub Workbook_Open()
Dim MyDate As String
MyDate = Format(Now, "mmm-dd")
ThisWorkbook.SaveAs FileName:=MyDate & ".xls"
End Sub
'-------------------------------------------------

Regarding the <<will probably give me a #REF>>
I do not see any problem with this because
1. A lot of the time there is no problem because if, for example, a SUM() formula is being used, the empty cell counts as zero.
2. It is usually possible to adapt other formulas to come to zero if no data is present.

I suggest you come back with any specific problem.

 

[samchox]

Brian,

Thank you for taking the time. My concern here was, Lets say UserA, Userb and UserC auto save their sheets on various dates, namely...

UserA 1 15.xls
UserA 1 20.xls
UserB 1 29 .xls
UserC 1 29.xls

Now each of these sheets have a certain number, say on cell A30 whick I want to total up in MasterWorkbook.xls, datewise.

I wouldnt know which day each user will enter data, so If i have to create a sum function across all these sheets, i would get REF/#, how do i make the Sum formula dynamic so that as soon as a user saves a sheet on a certain date, a formula gets cvreated to acquire that data from cell A30 from each of the relevant sheets on a given date.

I think now ive confused myself!

Anyways, if you can figure this out, please let me know the way to resolve it.

Thanks!

 

[BrianB]

I suggest you try this out because I still do not see any problem. If a *workbook* is 'missing' the cell will remain with the linking formula until you open the right one.