adding specific cell with todays date

  • Thread starter Thread starter andrew21
  • Start date Start date
A

andrew21

i found this script elsewhere in the message boards and have been tryin
to apply it for my own purposes(with ranges modified):

Range("A5").End(xlDown).Offset(1, 0).Value = Date
Range("B5:X5").End(xlDown).Offset(1, 0).Value = Range("B3:X3").Value

I have approx 23 columns from B3:X3...some of the cells are paste
links from other workbooks and some are blank(but will contain formula
later when there is enough data to work with)...basically i want t
build a list of the daily values from B3:X3 lower down the sheet with
new row added each day...the date going into column A....i went t
Tab--View Code---and enterd the script into General Declaration
without success...then i enterd it into Worksheet EventChange but tha
gave me Error 1004-application defined error...when i debugged i
highlighted the first sentence of the script(Range...=Data)....i a
pretty new to all this so any suggestions much appreciated
andre
 
Andrew,

You need to be a lot more specific about what you want to do: where the
values come from, where they get added, the logic for where to add them,
etc.

HTH,
Bernie
MS Excel MVP
 
okay...B3:X3 are cells that are constantly updating(sometimes second b
second)and were paste specialed from other cells in othe
workbooks(which are themselves updating from a DDE link)...we ar
talking stock symbol data...at the end of the day the updating wil
cease ...i would like to preserve the final values from this row lowe
down on the sheet with todays date...and so on preserving a new ro
each day so that eventually i will have many days of data...from this
will then be able to perform calculations on this data...but first
have to create these dated rows..
 
Andrew,

If you want to save them at the end of the day, you could use the
beforeclose event: copy this code, then go into the VBE and locate the
ThisWorkbook object of the workbook you are using, double click that object,
and paste the code in the window that appears. When you close the workbook,
the data will be appended to the bottom of the table. If you have more than
one worksheet in the workbook, you should further specify the
worksheet -change the four instance of Range("....") to
WorkSheets("SheetName").Range("....")

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myRow As Long

myRow = Range("A65536").End(xlUp)(2).Row

Range("A" & myRow).Value = Date
Range("B" & myRow & ":X" & myRow).Value = Range("B3:X3").Value
ThisWorkbook.Save

End Sub

HTH,
Bernie
MS Excel MVP
 
Thanks for the help Bernie...i couldn't figure out what you meant b
going into the VBE and finding Workbook objects...so i just went t
Tools--Macro---VBE with the workbook open and pasted your cod
(actually changing the range to reflect worksheet name as per you
instructions...closed the workbook...but it doesn't seem to work..n
data is appended that i could find nor any dat
 
Andrew,

From Chip Pearson's web site, specifically

http://www.cpearson.com/excel/events.htm

Start Quote -

Programming Event Procedures In VBA
Since event procedures are stored "behind" the objects that contain them,
you do not include them in standard Code Modules. Instead, you create them
in the modules that contain them. If you create an event procedure in a
standard Code Module, Excel will not recognize it as a valid event
procedure, and it will never be executed.

In the VBE, open the Project Explorer window from the View menu (or just
press Ctrl+R). In this window, you will see a "tree-view" listing all of the
open workbooks. Locate your workbook in the list, and expand that branch.
You'll see a "folder" called Excel Objects. Expand this branch of the tree.

There will be an icon for each worksheet and chart sheet in your workbook,
and an entry called ThisWorkbook. To add a Worksheet level event, double
click on the appropriate worksheet icon to open up the code window for that
worksheet. (You can also right-click on the worksheet icon, or on the
worksheet tab in Excel, and choose View Code from the pop-up menu.) At the
top of the code window, there are two drop down edit boxes. In the one on
the left, select Worksheet and in the one on the right, select the name of
the event you want to add. Excel will automatically insert the Private Sub
Worksheet_event (parameters) statement and the End Sub statement.

If you want to add a Workbook level event, open the code module for the
ThisWorkbook item, and follow the procedures listed above. The only code
that should be in these modules is event procedures, and declarations for
the modules. Do not put your own macros and functions in these modules.

-End Quote

If you can't get this to work, email me privately (take out the spaces and
change the dot to .) and I will send you a working version.

HTH,
Bernie
MS Excel MVP
 
Bernie it works like a charm now...thanks so much...hate to trouble yo
more but i'm wondering if the newest saved row of data could be place
at the head of the saved rows rather than appended to the bottom as i
the case now...i assume it would be easier to set up calculatons thi
way...for instance i would need to do a 10 Day MA of the most recent 1
days data and it would be simpler to costruct if i could always specif
the data in cells B3:B13(for example)...also i probably only need t
collect the latest 30-50 days worth of data..so having entries on ro
150 is superfluous..any ideas on how this could be done?thanks again
andre
 
Andrew,

Change the body of the code to

Range("A4").EntireRow.Insert
Range("A4").Value = Date
Range("B4:X4").Value = Range("B3:X3").Value
Range("51:60").Delete
ThisWorkbook.Save

Also, note that any calculation you set up should incorporate OFFSET rather
than a specific range: B3:B13 will become B3:B14, so instead of simply

=SUM(B3:B13)

use

=SUM(OFFSET(B3,0,0,10,1))

as that will be 'immune' to row insertions.

HTH,
Bernie
MS Excel MVP
 
Bernie
that is awesome...works perfectly...one final question(we hope!):i'v
put 2 named sheets in the workbook and wish to perform the sam
operation at close on both sheets...as it stands now it will only wor
on the active sheet...is there a syntax for including both sheets i
the code?...i tried WorkSheets("name1""name2").Range("B3:X3") but tha
caused probs
andre
 
Andrew,

Those changes would cause problems <vbg>

Change the code to:

Dim mySht As Worksheet

For Each mySht In Worksheets(Array("name1","name2"))
mySht.(Range("A4").EntireRow.Insert
mySht.Range("A4").Value = Date
mySht.Range("B4:X4").Value = Range("B3:X3").Value
mySht.Range("51:60").Delete
Next mySht
ThisWorkbook.Save

HTH,
Bernie
MS Excel MVP
 
Bernie
i guess i didn't explain myself sufficiently...the values in B3:X3 o
sheet1 differ from the values on sheet2..the code you've provided work
but what happens is the closing values from sheet1 are replicated o
sheet2 rather than saving the closing values from sheet2 as well...fo
anyone trying this at home there is an extra paranthesis that is no
needed from this line of the code: mySht.Range("A4").EntireRow.Insert
appreciate all your help Bernie--also the tip about Offsets
andrew

on further investigation what happens is that whatever sheet is activ
at close those values are transposed to the inactive sheet as wel
 
Andrew,

Sorry about that - I was braindead, and didn't test my code. Change

mySht.Range("B4:X4").Value = Range("B3:X3").Value
to
mySht.Range("B4:X4").Value = mySht.Range("B3:X3").Value

HTH,
Bernie
MS Excel MVP
 
Bernie
it works perfectly now...thanks a million for all your help...i've eve
begun to grasp some of the coding procedures involved...
the Excel hook is set deep
andre
 
Back
Top