Date controlled function execution

[Guest]

Does anyone know of a way to make a cell function occur on a schedule. I
have a spreadsheet where the contents of a cell on one worksheet are
transferred to a cell on another worksheet once a week. If someone forgets
to manually do this it makes analysis rather troublesome. I would like this
to happen automatically on a schedule. Is this possible?

 

[Bob Phillips]

You could use the Workbook_Open and test that for your target date, and the
transfer the data if so.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob, but I think I am thinking beyond the scope of my ability, as I
have no idea what Workbook_Open is.

 

[Bob Phillips]

Okay, let's take you ability up a notch <g>.

Something like this will form the basis of what you want. Just change to the
correct workbooks, sheets and ranges.

Private Sub Workbook_Open()
Dim oWBThis As Workbook

If Date = DateSerial(2005, 7, 21) Then
Set oWBThis = Workbooks.Open(Filename:="C:\MyTest\18.xls")
oWBThis.Worksheets("Sheet1").Range("A14").Copy
ActiveWorkbook.Worksheets("Sheet1").Activate
Range("A22").Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
oWBThis.Activate
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)