Autosaving an excel sheet as current date

samchox

New Member
Joined
Feb 2, 2004
Messages
29
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?
(y)
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

New Member
Joined
Feb 2, 2004
Messages
29
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! :biggrin:
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,140
Members
425,263
Latest member
alcat

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
Top