Recording data using date

[Mark4pm]

Hi,

I was wondering if it was possible to have excel record data on a particular date.

I have a situation where I want a spreadsheet to be used continuously throughout the year but then every month or two to take a snapshot of all the data to be referenced later and be used to show a progression.

E.G.
If I was recording distance travelled throughout the year, the numbers would continue to rise throughout. I would like the spreadsheet to record the distance travelled in a separate sheet at the end of January, February and so on automatically whilst allowing me to continue to work on/with the cumulative data.

Thanks

[protonLeah]

Use the Workbook_BeforeClose event. Check the tracking sheet for the current date: if it's not there add the date and tracking info, else just close. ... ?

[Mark4pm]

Thanks but I'm not really sure what that means. I'm teaching myself as I go along to be honest.

I found this Visual Basic code which took the data from the active cell and put it on a cell in another sheet.

Please Login or Register  to view this content.

Could this be adapted to take data from a number of specific cells and place the data in specific cells on another sheet? I have no idea where to start with it but I would be happy to set up a series of buttons to copy the data rather than using the date.

Thanks

[protonLeah]

Rather than a macro, you can use summary table with formulas for monthly totals. In the sample file, I used named dynamic ranges

amount =Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$A:$A))
date =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

that expand as data is appended to the list and a summary table per month using:

Please Login or Register  to view this content.

to calculate monthly totals.

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[Mark4pm]

Sorry.

New to this

[Mark4pm]

Thanks for this but what I need is a little more complicated.

What I'm using it for is this...

I am setting up an assessment system for my school. When children meet certain objectives they are given a score and this leads to a grade. As the year progresses the score will increase and the grades will change. What I am looking for is a way to take a snapshot of the grades for the entire class (in a variety of subjects) at various points throughout the year. I would then be able to see the progress the children have made term on term throughout the year.
The 'grade' cell would need to continue to be used througout the year and the value copied into specific cells in another sheet. I originally hoped excel could automatically copy these cells on or after a particular date but failing this, was happy to create a button to copy all the necessary cells in one go.

Hope I have made this clear. I'm not finding it easy to explain.