Need help with date manipulation

[Lostinspace]

Newbie, here. Just gotten started with VBA over the last month or so, so i am still on the not so steep part of the learning curve.

Here's my problem. I have an excel workbook with a worksheet for each of the 5 weekdays. At the start of each monday i want to get vba to take certain values from the previous friday and insert them into monday after clearing the cells (it is a fresh week after all!!). Part of the problem is that the spreadsheets will be accessed several times each day, so the program will need to recognize that it is a new monday once and then keep track of the dates after that.

It seems everytime i try to do this, all of my parameters are based on the current date and essentially the process keeps resetting its own baseline.




Help.

[MWE]

Newbie, here. Just gotten started with VBA over the last month or so, so i am still on the not so steep part of the learning curve.

Here's my problem. I have an excel workbook with a worksheet for each of the 5 weekdays. At the start of each monday i want to get vba to take certain values from the previous friday and insert them into monday after clearing the cells (it is a fresh week after all!!). Part of the problem is that the spreadsheets will be accessed several times each day, so the program will need to recognize that it is a new monday once and then keep track of the dates after that.

It seems everytime i try to do this, all of my parameters are based on the current date and essentially the process keeps resetting its own baseline.




Help.

Hi and welcome to the forum.

There are many ways to trigger events and then make sure the event does not retrigger until the appropriate next time. Personally, I like to use a "housekeeping" sheet for this sort of thing and keep it hidden most of the time.

For your case, you want to initiate the cleanup each Monday, so you probably want to add some code in the ThisWorkbook code module, in particular, the Workbook_Open() procedure that:

1. tests for the day of the week; if Monday, continue, else exit
2. examines the housekeeping sheet to make sure the cleanup has not already been done for this Monday
3. if not already done, do the cleanup and update the housekeeping sheet

Of course, this assumes that the spreadsheet runs every Monday. If it does not, you will need to check each time it runs whether it is Monday or not. Actually, this is a little easier because you simple check the housekeeping sheet to see when the cleanup task last ran. If, this week, then do not run the cleanup task. If last week (or earlier?), then run the clean up task.

[Lostinspace]

thanks for the input.

To be honest i couldn't figure out how to do your point #2, which would be very helpful to me. Could you point me to a source or give me a code example on how to do that.

[Bob Phillips]

To be honest i couldn't figure out how to do your point #2, which would be very helpful to me. Could you point me to a source or give me a code example on how to do that.

The sort of thing you would do is to store a value in a cell when the first work of the week is done, whether that be the first time on Mon, or the first time some later day.

In your end of week tidy-up, which I presume you have, you would then clear that value, ready for the next week.

[MWE]

The sort of thing you would do is to store a value in a cell when the first work of the week is done, whether that be the first time on Mon, or the first time some later day.

In your end of week tidy-up, which I presume you have, you would then clear that value, ready for the next week.

xld's comments are similar to my thinking. The housekeeping sheet (I typically call it "Housekeeping") is created and a particilar cell is selected for keeping track of when the cleanup is done. Let's say it is cells B2. The last step of the cleanup is to write the date and time to cell B2. The next time the spreadsheet opens, the "check code" examines Worksheets("Housekeeping").Cells(2,2) to see if the cleanup has been "recently" done. If so, cell B2 is left alone. If a new cleanup is appropriate, it is done and cell B2 updated.

Alternatively, you may wish to keep track of every time the clean-up procedure was run. In that case, you allocate, say, col B for the date. Each time the spreadsheet is opened, the LAST date in col B is checked. If it is time for another cleanup, the cleanup is done and the current date/time is written to the next row in col B.

Since the housekeeping sheet is pretty important and if corrupted will create problems, it is best to keep it hidden and protected. You can read from a protected sheet, but you can not write to it. So if you needed to write to the sheet, you would unprotect it, write to it and then reprotect it. Something like:

Sub CleanUp()
Dim LastDate As Date
'           check last date
LastDate = Worksheets("HouseKeeping").Cells(2, 2)
'           do cleanup
'           update HouseKeeping sheet
Worksheets("HouseKeeping").Unprotect
    Worksheets("HouseKeeping").Cells(2, 2) = Date + Time
    Worksheets("HouseKeeping").Protect
End Sub

[rbrhodes]

Hi,

Make a copy of your workbook to test with. Put this code in the ThisWorkbook Module:

Private Sub Workbook_Open()
'
'Checks Date to see if 'Cleanup' needed
'
Dim nextrun As Date
'Gets the Monday from hidden sheet 'DateSheet'
nextrun = Sheets("DateSheet").Range("a1")
'Checks if date has past. If not then do nothing
If Now() < nextrun Then
	Exit Sub
End If
'If date is today or later do your cleanup code - ONCE
 
'	...your "Cleanup" code goes here...
 
'Sets next run date as following Monday 's date
	Sheets("DateSheet").Range("a1") = nextrun + 7
End Sub

And this in a standard module:

 
Sub CreateHiddenSheet()
'
'Run this only once
'
'Adds a Sheet for "housekeeping?" with the name of 'DateSheet'
	Sheets.Add.Name = "DateSheet"
'Gets a Monday date from you to start with
	ActiveSheet.Range("a1") = InputBox("Enter a Monday date to start with")
'Hides the sheet from the Format/Unhide menu
	Sheets("DateSheet").Visible = xlVeryHidden
End Sub
 
 
Sub UnhideDateSheet()
'just in case tou need to see it
	Sheets("DateSheet").Visible = True
End Sub
 
Sub RehideDateSheet()
	Sheets("DateSheet").Visible = xlveryhidden

To use it: Run the CreateHiddenSheet macro and give it a Monday's date to start with ie: 6/6/2005 or 13/6/2005. Save and close your workbook. Open it again and you should be in business. The CreateHiddenSheet macro will never be used again....but will give you a hidden sheet in your workbook

If you need to change the hidden sheet then you'll have to run 'UnhideDateSheet', change it and then run 'RehideDateSheet'.

If you want, you could protect the hidden sheet too, as long as you include a line that says "Application. userinterfaceonly=true" so the macro can update the protected sheet...


And here's a bonus!

 
Sub PostStaticDate()
'
'Bonus Sub: How to set a static date:
'
	 Range("A1") = DateSerial(Year(Now), Month(Now), Day(Now))
End Sub

Thats probably enough for now!

Cheers,

dr

[Lostinspace]

Muchas gracias.


Those are great ideas, i am sure i can use them

Last night i also came up with something that works, but certainly it is not as elegant as these examples. When i get of from work i will post my simple solution but for now i really appreciate the input.