I am have average excel knowledge with formulas and I am relatively new to the creation of Macros. I was told by my boss that he wants me to create a Macro that will palce a time stamp in the bottom left corner of the page (via the footer) that updates automatically every time a sheet is worked on. Please provide any help or assistance that can get me through this task.
View 11 RepliesI am trying to create a time stamp that shows elapsed time. So I enter my start time, and if I enter any text in cell B7, return the elapsed time in A7 based off of start time in A6. I have attached an example workbook.
View 2 Replies View RelatedI am not sure that I can do this, but here is what I would like to do. I have a worksheet that I initial when I start a job in on cell and then when I finish in another cell. What I's like is to have a macro running in the back ground that will tell me the total elapsed time from when I started to when I finished.
View 5 Replies View Related device a macro which should be able to provide a time stamp in Column B when the adjecent cell in Column C is updated.
i.e. If C1 is updated, B1 should get the time stamp. *& if the value in C1 is removed, then the time stamp in B1 should also be removed.
In addition to this, I would require the macro to unprotect the sheet before updating the timestamp and protecting it again after the time stamp has been enetered. further moving on to saving the sheet.
I tried to combine few updated macros to complete it bu have been unable to do so.
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 need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
[Code] .........
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
FC_Macro_Sample.xlsm
I want to add hours to a date-time cell to get result in date-time.
Format of cell A1 is d/m/yy h:mm AM/PM
Format of cell A2 is General
Format of cell A3 is d/m/yy h:mm AM/PM
I want to add A2 (number of hours) to A1 to give A3.
The formula I used is A3=A1+Time(A2,0,0)
The formula works perfectly fine when A2 is less than 24, but when A2 is more than 24, the date doesn't get changed.
I am looking for a macro that will allow me to create a dynamic range based on the settings I give it. I would like the dynamic range to be called tracking_number and it to have a reference to =Offset(MainReport!$A$2,0,0,COUNTA(MainReport!$A:$A),1)
If this is possible I would really appreciate it. The reason I need this is because I have 2 workbooks. They are the one with the MainReport in it and then the Master workbook. Every day the workbook containing the MainReport is overwritten with more data, so I can not store a dynamic range and reference it each day.
i want to create dynamic Excel Charts in one sheet ,The sheeet contain dynamic columns and rows and i want to create for each 4 columns a chart in a new sheet ,i started to try it in VBA and didnt founs the way to select 4 columns as a range, i succeseed to do it with msgboox that the user enter the range but i want to do it in a loop
View 9 Replies View RelatedI have an array starting at, lets say "C25" -header line Row 24- with data entry and calculations to Colume "AW25".
Each row represents a data record for a project, where different data types are used (Number & Text).
A "termination" row is set as last row.
New records are always enterd in row "25", but any row in the array is allowed to be deleted.
The number of rows can be extended or reduced, when entering/ deleting a new record (not all info are mandatory for calculations) - basic data is followed by lots of calculations for the record.
This leads to the issue that the array is dynamic regards amount of row.
To make it even more "fancy", the array can be sorted using data filter from row "25" to "last row -1".
I need to track when a record (any cell in the row) has been accessed/ modifed/ enterd last. The time stamp may only be altered when the particular record (row) is modified and NOT in case any of the cells is modified (NON-valotile)
I have foreseen to enter that date in Colum "Bxyz" of the array.
For a referrence I still have Colume "A" as "spare".
Depending on time difference I've to mark the "date" cell with conditional formating (this is an easy task I know how to do...).
Does any one has an idea how to implement such routine (sub or Function)..
Remeber, number of rows varies - up to now there are just 310 records, but will be much more in future.
Not to forget ... some of the Colums have data avalidation set...
How can I make a hot key that puts todays date and my name on it so I don't have to type it over and over again?
I have a system of checkboxes and now I need a time stamp when the check box was marked TRUE. They ar linked to cells so the solution can be either linked to a cell or to a checkbox. Obviously I can't use Today or Now functions because they are refreshed. I found many solutions for time stamps when a change is made in a cell, but none of them worked for me. I tried altering them with no succsess. I would be very happy if the solution would apply to the whole column, not one cell and checkbox, because I have A LOT of checkboxes.
The checkboxes are in column B and I want the time( date) stamps in column C.
I've got a simple code that creates a new text file in which progress of the macro is logged. I wanted to add also time stamps at the end of each line, so that the user can see when a given stage of the macro finishes working.
[Code].....
And then, in various portions of the code, after certain jobs are finished, I simply add:
[Code] .....
And it all works fine, only the problem is the time stamp is not updated... So say I run the macro at 10:00 - the time stamp on each line shows (date) 10:00...
How do I create a date stamp in a work sheet, if lets say anything gets revised or updated the date gets updated as well?
For Example: I want to put the date stamp in A1. So when anything gets revised in the document, A1 will have the date when that document was revised.
i type into any cell in row 1 i need the time at that moment to be captured and shown in cell "B". When i type into row 2, the time which i entered the data must be captured into row 2 cell "B".
View 5 Replies View RelatedI have been able to use a time stamp code to put a date and time in a cell... I then worked out how to use this to list dates and times in the same cell (instead of replacing the cells contents)... using this
View 7 Replies View RelatedThis time stamp macro is great but I would also like to record the person who said 'Y' in the same cell as the date stamp '12:00p.m. MWatson'. The persons name should come from the Username.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range([P13], Cells(Rows.Count, "p"))) Is Nothing Then 'change your range on this line"
With ActiveSheet
If Target.Value = "Y" Then
Target.Offset(0, 1).Value = Now
[Code] ........
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"
End Sub
I can't figure out how to get a date/time stamp to show when the spreadsheet was last updated.
View 9 Replies View RelatedI 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
Application.EnableEvents = True
End If
End Sub
I have 2 barcode scanners on opposite ends of a conveyor. Barcode data from reader 1 inputs the data in Column A. Reader 2 will be inputting its data into J2. When J2 has data inputed, I would like to match that data to column A. On the row where the match is found, I would like to transfer a "Yes" to column H and a time/ date stamp in column I. Once this has occurred, clear the data from J2. I will have a minimum of 20 seconds between the scans on the second reader.
View 2 Replies View RelatedThe following code was supplied by Bill, but I want to do the same thing in B10:B164, where "x" is entered in B10 and the time stamp is entered in C10 & D10.
I tried copying the same script but had an Compile error message which said Ambiguous name detected: Worksheet_Change. The name of the sheet is Sheet 1 (Main)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("M10:M164 ")) Is Nothing Then 'change your range on this line"
If Target.Value = "x" Then
Target.Offset(0, 1).Value = Now
Else
Target.Offset(0, 1).Value = ""
End If
End If
End Sub
I have tried several macros and different code to accomplish the following:
1. scan a 3x9 barcode via hand-held scanner to populate a cell in column A, lets say A2.
2. the scanner does this and puts the number, say "330030" there (via the forms function)
3. in column B I would like it to say the date and time of the scan
4. the macros I have used will 'sometimes' populate the date / time but when I reopen the sheet, everything changes to 'NOW'. I would like the data to not change the date.
This is for a simple inventory spreadsheet that I am building to track movement of stock.
I am looking for a command that will time stamp a cell with just the time, not the date. I've already looked at =Now(), but that, unfortunately, includes the date as well. I just need the 24hr value.
View 2 Replies View RelatedI 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)
I've got a web query setup to poll a table detailing traffic information from my state's DOT page.
I have the web query set to update every 5 min.
I would like to save a particular cell from this query every 5 min and place a timestamp next to it.
Ultimately, I would like to build a table, one column = time, the other column = traffic data and have each row be 5 min apart.
I am trying to automatically date/time stamp a row when I copy entries I purge from other worksheets.
View 3 Replies View RelatedThe "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....
View 9 Replies View RelatedI have a large number of cells with time stamps: 00:07:45 (hh:mm:ss)
What I would like to do is just show the mm - but NOT format the cell as [mm], just as a general / number '7'.......
I'm trying to have excel insert a timestamp on a specific range of cells. I have it currently working on one cell, but can for some reason am having trouble getting it to work on the entire range... Here is what I have currently...
PHP Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$E$23" Then
Target.Value = Time
End If
End Sub
I would like this to work for the range E23:E28 and F23:F28 I'm sure its something simple that I'm not doing...