Hi All, I want to set up a macro that will input a date stamp for the working day before this one. I have to input the status of dozens of meeting rooms everyday and the checksheets that I work from are from the previous working day (So on a Monday, I want the Macro to enter Friday's date). I wanted to create a quick macro to save myself the hassle of entering the date for every entry and obviously, if I incorporate the TODAY() function it will update every time I open the workbook and give me the wrong date.
I've been checking related threads and can't seem to find either a VB code or a function that'll enable me to do this (I haven't looked particularly hard as I'm at work ).
If a1,a21,a41 have a value greater than 0, I wish to stamp cell b1,b21,b41 and so on with todays date, but without the stamped value changing/advancing tomorrow. At the moment cells b1,b21,b41 are copied down as follows. =if(a1>0,TODAY(),"") This works fine, but the date stamp of course changes tomorrow. I can not apply code to the whole b column as cells b2:b20, b22:40 etc have other non-date format data to which the code does not need to apply - the todays date value cell occurs every 20 cells.
How can I add a date stamp that will populate a field when text is entered into another cell but will not change every time I re-open the sheet. I have been using =if(B4="",NOW()) which changes each time though and as soon as text is entered it disappears. I want to capture the actual date that someone enters text into a cell and save that date in another cell???
In my job, my team have to take phone-calls from stores asking various questions. In order to combat this, I have set up a call tracker to see where the calls are coming from (so we can beat them with a stick). I have asked my colleagues to record the date and time of the calls.
I need a big button at the top of the page that says 'New Call'. Then, when this button is pressed, the time and date stamp will automatically be put into cell A3, then when pressed again a4, and again a5... and so on.
I am struggling with the following:
-Using CTRL + Shift + : to input the date/time on a macro inputs the date I made the macro, not the date/time the button is pressed! -I can only get the date to go in cell A2 - If I press it again, nothing happens. how to do this, or just do it for me
I have a worksheet that has a sent date and expected delivery date I need create a macro that will alert me if today's date is within 5 days of expected delivery date.
I am trying to compare a row of dates (row 1) and need to compare to today and write future under dates in the future. I am doing this in VBA, it is part of a bigger macro. This is the outcome I am looking for:
My spread sheet is a church offering register that is used to record weekly contributions. Column A contains the names of the individual contributors. Columns B through BA are used to record the weekly contributions for each of the 52 weeks of the year. Row 1 of columns B through BA contains the Sunday date MM/DD/YYYY. I would like to have a macro that would scan those cells looking for a date < today. If that condition is true, I would like to hide that column. When date = today or date > today the macro can end. The goal is to have display the current week's column immediately following Column A.
I've been using the following code and would like to add another IF that checks the value of a cell to see if it holds today's date.
Code: If Range("E" & CStr(LSearchRow)).Value "Closed" Then If Range("E" & CStr(LSearchRow)).Value "Closed w/o Customer Confirm" Then 'Select SR in Sheet to update Range("B" & CStr(LSearchRow)).Select
[Code] ......
I tried to modify it to...
Code: If Range("E" & CStr(LSearchRow)).Value "Closed" Then If Range("E" & CStr(LSearchRow)).Value "Closed w/o Customer Confirm" Then If Range("AK" & CStr(LSearchRow)).Value "=today()" Then 'Select SR in Sheet to update
[Code] ......
I've tried a couple of other things but no success
I'd like a macro to rename a worksheet from its current name of "FullScreen (2)" to say Numbers, plus today's date (without the plus) For example... Numbers as of 02-17-09
I have a row that contains each date for the year in B5:NB5. I would like to have a button or macro that will jump to the cell containing today's date.
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
I have a spreadsheet (obviously), I want to click a check box and have it date stamp with text, in the next available cell... if you know what i mean! I have attached a screenshot of what i have (the check boxes) and what i need (the writing in blue).
I would like to take this VBA a step further. What I want to achieve is to date & time stamp a worksheet in cell A1 if any other cell within the sheet is altered. Below is the VBA I used today to enter the info if cell A1 is changed. Can anyone please help with my new target.
Just one more question: When a shared file is used is it possible to also enter the username of the person who's made the alteration.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range ' Only look at single cell changes If Target.Count > 1 Then Exit Sub ' Set Target Range Set rng = Range("A1") ' Only look at that range If Intersect(Target, rng) Is Nothing Then Exit Sub ' Action if Condition(s) are met Target.Offset(, 1) = Format(Date, "d mmm yyyy") & " " & Format(Time, "h:mm") & " Hrs"
I am struggeling to adapt this code to what I need. As is, it simply puts a date stamp into cell AJ2 when anything is changed in A2-AJ2. While this code works great I have been attempting to apply it to the rest of the columns in the work book with out having to list each of the rows and cells in the code.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, [A2:AI2]) Is Nothing Then [AJ2] = Date End If End Sub
(this is obviously the code that runs correctly)
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, [A2:AI2-A10000:AI10000]) Is Nothing Then [AJ2-AJ10000] = Date
End If End Sub
(this is one of many unsuccesfull attempts to alter it)
I found the code below in a previous tread, it works great, but i need some help altering it. I have it set-up when someone puts their initials in column B the time and date inputs in column S. But it also inputs if I hit delete or clear contents when I need to clear the form. Is there a way to make it input the time and date ONLY when letters are entered in column B?
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then Application.EnableEvents = False Target.Offset(0, 17).Value = Now
I've got a sheet with lots of columns. Each time someone makes a change in any row of any one of those columns, I want it to date stamp it in an extra column on the end so we always know who made the last change to each row and when. The actual entering of the data isn't a problem, the problem I'm having is getting it to always put the data stamp in the right column or row.
I am trying to automatically capture and record the date of a cell's last change in value (date stamp). I have an item price list and if a particular cell gets updated I want to automatically record the date of change of that cell. I realize that after I change cell A1 I could tab to cell B1 and enter Ctrl+; but if I have a hundred new prices to enter I don't want to do that (plus me or the data entry person might forget).....
I copied the code that was used to inserting the date when the cell next to it was updated, the original post can be found here: [URL]
The Code below will check a range of cells between c3 and c20 and if I make a change to the value in any of them, then the cell to the right of them will have the date inserted. I've had to modify the original code from the other post a little bit to stop an error appearing when I insert a new Row:
[Code] ....
On the first example that was posted it all ran ok until until I attempted to insert a new Row, then it would put the date into about 5 of the cells to the right of where it should do and I received an error message with the usual Debug stuff on it. It would also delete my column descriptions that I had on Row 2.
Would it be possible for it to not auto insert the date on any new blank row that I insert?
What would be the correct range for me to get the code to work on c3:infinity....
Is it also only possible to enable macro's and code like this in the current document instead of every document that you load through excel.
I want to create a command button on one worksheet so that when I click it a pop up box asks the user for the date and this date is input automatically into a specified cell on another worksheet.
I have got this far - which makes my data input box pop up but now I need adding in code to put that data in a certain cell - for example Cell B1 in worksheet Daily Tasks.
Private Sub CommandButton12_Click()
Dim ans As String ans = InputBox("Enter date dd/mm/yy", "Data Entry")
I am having a very difficult time making this work, if there is anyone who can assist me, I would greatly appreciate the assistance. You can see below what I am trying to do. The entries between the last cells H,3 & K,3 just contain data that is not affected by the rest of the sheet functions.
Cell one (A,3) has a drop down menu for vehicle status. (B,3) vehicle number (C,3) Drop down vehicle type. (D,3) is for dept. (E,3) Date/Time stamp auto generated from (A,3) entry. (Vehicle Status) (F,3) is POC. (G,3) Phone/email. (H,3) Date & Time stamp out of shop. (This cell will recieve the date & Time Stamp from entry of work done in (K,3)
Found this macro in a differant thread for a date stamp in A1. I changed it to C38. Also I have it in the workbook module. It is supposed to update the date stamp on save. I think if the data has been changed or not.