Hi:
SheetPlan should be worksheets("Plan")

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SheetPlan.Range("A1") = DateSerial(2007, 3, 5) Then
' XXXXXXXX
Worksheets("Plan").Range("B43") =
Worksheets("Summary").Range ("E14")
End If
End Sub

for multiple dates try an if statement:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
dim dc as date ' hold the date from the worksheet
dim sCellAddress as string ' hold the address of the cell

dc=worksheets("Plan").Range("A1")

If dc= DateSerial(2007, 3, 6) Then
sCellAddress="B43"
elseif dc= DateSerial(2007, 3, 7) Then
sCellAddress="B44"
elseif dc= DateSerial(2007, 3, 8) Then
sCellAddress="B45"
elseif dc= DateSerial(2007, 4, 1) Then
sCellAddress="B46"
End If
Worksheets("Plan").Range(sCellAddress) =
Worksheets("Summary").Range ("F14")

End Sub

But I would rather use a lookup table in the workbook say on sheet called
'lookup' with vba where in your lookup table you have

date destination_cell destination_worksheet
=date(2007,3,6) B43 Plan
=date(2007,3,7) B44 Plan
=date(2007,3,8) B45 Plan
=date(2007,4,1) B46 Plan

you can then do:


Const csz_wsLookupName As String = "lookup"
Const csz_rLookup As String = "A:C"
Const ci_ColCell As Long = 2
Const ci_ColSheet As Long = 3
'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
' Cancel As Boolean)

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim dc As Date ' hold the date from the worksheet
Dim sz_CellAddress As String ' hold the address of the cell
Dim sz_Sheet As String ' hold sheet name
Dim a ' answer
dc = Worksheets("Plan").Range("A1")
a = Application.VLookup(CLng(dc), _
Worksheets(csz_wsLookupName).Range(csz_rLookup), _
ci_ColCell, False)
If Not (IsError(a)) Then
sz_CellAddress = a
a = Application.WorksheetFunction.VLookup(CLng(dc), _
Worksheets(csz_wsLookupName).Range(csz_rLookup), _
ci_ColSheet, False)
sz_Sheet = a
Worksheets(sz_Sheet).Range(sz_CellAddress) = _
Worksheets("Summary").Range("F14")
End If

End Sub


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

On Mar 7, 6:48 pm, Martin Fishlock
wrote:
Hi:

You can't preserve the value on day this an accumulating total

there are a couple of work arounds but they depend on the data.

I would suggest using an autosave effect in VBA to pick up the value and
dropit into the specific cell as in:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheet1.Range("A1") = DateSerial(2007, 3, 5) Then
Worksheets("Sheet1").Range("B1") =
Worksheets("summarySheet1").Range("F14")
End If
End Sub

you need to modify this as the date is hard coded in the formula and the
cell answer is b1 on sheet1 for which I didn't know where you wanted it.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.



" wrote:
When I perform this function, after (2007,3,5) the cell output is
"0".
I need for the information that is displayed on (2007,3,5) to be
permanent and not
change to "0" when (2007,3,5) has passed.
=SUMIF(A1,"="&DATE(2007,3,5),Summary!F14)

I can't use this formula because I am displaying accumulating data
every week and it will be overwritten with the new data and not
preserve the old information.
=SUMIF(A1,"="&DATE(2007,3,5),Summary!F14)

Thanks for any suggestions you can give me!- Hide quoted text -

- Show quoted text -



Thank you very much for your help!
I think this might work for what I want to do.
I do not have experience with vba code, so I am getting errors when I
try to save it.
This is what I have entered:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SheetPlan.Range("A1") = DateSerial(2007, 3, 5) Then
Worksheets("Plan").Range("B43") =
Worksheets("Summary").Range ("E14")
End If
End Sub


I would like to do this for numerous dates and display the data in
different cells.
Do I have to create a new workbook for each instance of code for a
different date?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SheetPlan.Range("A1") = DateSerial(2007, 3, 6) Then
Worksheets("Plan").Range("B43") =
Worksheets("Summary").Range ("F14")
End If
End Sub


Another question...
Can I grab the information from a cell in a pivot table and display
it?
Do you know the syntax?
Here is what I have...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Sheet11.Range("A1") = DateSerial(2007, 3, 7) Then
Worksheets("Sheet11").Range("D43") =
Worksheets("summarySheet12").Range ("GETPIVOTDATA("mnemonic",Summary!$A
$2,"status","NOK"))
End If
End Sub

Thanks and Cheers!