Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to add todays date (static) to the current active cell using m
Hello,
I have a worksheet that I'd like to apply the current date and time (adjacent cells) in the next blank row auotmatically when the worksheet is opened. i.e. if row 10 is blank on open, then A10 will have the date applied and A11 the time. Both values must remain static. I've started by trying to write a macro that will insert the current date value into the active cell. See code below: Dim james_stamp As Date Dim james_range As Range james_stamp = Now() james_range = ActiveCell.Range Range("james_range").Value = james_stamp I'm having problems capturing the active cell and then using this value within the 'Range' function. My next challenge was replacing the active cell value with the 'next blank cell'. Then I'd need to run this macro on opening the worksheet. Can anyone push me in the right direction? Many thanks, JJ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to add todays date (static) to the current active cell using m
Dim DestCell as range
with worksheets("logsheet") set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0) end with destcell.value = now JimmyJam75 wrote: Hello, I have a worksheet that I'd like to apply the current date and time (adjacent cells) in the next blank row auotmatically when the worksheet is opened. i.e. if row 10 is blank on open, then A10 will have the date applied and A11 the time. Both values must remain static. I've started by trying to write a macro that will insert the current date value into the active cell. See code below: Dim james_stamp As Date Dim james_range As Range james_stamp = Now() james_range = ActiveCell.Range Range("james_range").Value = james_stamp I'm having problems capturing the active cell and then using this value within the 'Range' function. My next challenge was replacing the active cell value with the 'next blank cell'. Then I'd need to run this macro on opening the worksheet. Can anyone push me in the right direction? Many thanks, JJ -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to add todays date (static) to the current active cell using m
Something like the following should do it. Note that this should be saved in
the 'ThisWorkbook' module of the sheet in qusetion and it will run automatically when the workbook is opened. Private Sub Workbook_Open() ActiveCell.Offset(0, 1) = Now() End Sub HTH, TK "JimmyJam75" wrote: Hello, I have a worksheet that I'd like to apply the current date and time (adjacent cells) in the next blank row auotmatically when the worksheet is opened. i.e. if row 10 is blank on open, then A10 will have the date applied and A11 the time. Both values must remain static. I've started by trying to write a macro that will insert the current date value into the active cell. See code below: Dim james_stamp As Date Dim james_range As Range james_stamp = Now() james_range = ActiveCell.Range Range("james_range").Value = james_stamp I'm having problems capturing the active cell and then using this value within the 'Range' function. My next challenge was replacing the active cell value with the 'next blank cell'. Then I'd need to run this macro on opening the worksheet. Can anyone push me in the right direction? Many thanks, JJ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to add todays date (static) to the current active cell usi
Oops, I think I misread your post...better get new reading glasses.
The following change should take care of finding the first blank row in column A of "Sheet1". I also changed to create two cells, one for the date and on for the time: Private Sub Workbook_Open() With Sheets("Sheet1") If .Range("A1") = "" Then .Range("A1") = Format(Now(), "MM/DD/YY") .Range("A2") = Format(Now(), "h:mm:ss") Else .Range("A1").End(xlDown).Offset(1, 0) = Format(Now(), "MM/DD/YY") .Range("A1").End(xlDown).Offset(1, 0) = Format(Now(), "h:mm:ss") End If End With End Sub You can modify the date/time formats as needed with any date or time format that Excel recognizes. HTH, TK "T Kirtley" wrote: Something like the following should do it. Note that this should be saved in the 'ThisWorkbook' module of the sheet in qusetion and it will run automatically when the workbook is opened. Private Sub Workbook_Open() ActiveCell.Offset(0, 1) = Now() End Sub HTH, TK "JimmyJam75" wrote: Hello, I have a worksheet that I'd like to apply the current date and time (adjacent cells) in the next blank row auotmatically when the worksheet is opened. i.e. if row 10 is blank on open, then A10 will have the date applied and A11 the time. Both values must remain static. I've started by trying to write a macro that will insert the current date value into the active cell. See code below: Dim james_stamp As Date Dim james_range As Range james_stamp = Now() james_range = ActiveCell.Range Range("james_range").Value = james_stamp I'm having problems capturing the active cell and then using this value within the 'Range' function. My next challenge was replacing the active cell value with the 'next blank cell'. Then I'd need to run this macro on opening the worksheet. Can anyone push me in the right direction? Many thanks, JJ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to add todays date (static) to the current active cell usi
Fantastic - thanks guys for this, I'll give it a try. :)
Jim "T Kirtley" wrote: Oops, I think I misread your post...better get new reading glasses. The following change should take care of finding the first blank row in column A of "Sheet1". I also changed to create two cells, one for the date and on for the time: Private Sub Workbook_Open() With Sheets("Sheet1") If .Range("A1") = "" Then .Range("A1") = Format(Now(), "MM/DD/YY") .Range("A2") = Format(Now(), "h:mm:ss") Else .Range("A1").End(xlDown).Offset(1, 0) = Format(Now(), "MM/DD/YY") .Range("A1").End(xlDown).Offset(1, 0) = Format(Now(), "h:mm:ss") End If End With End Sub You can modify the date/time formats as needed with any date or time format that Excel recognizes. HTH, TK "T Kirtley" wrote: Something like the following should do it. Note that this should be saved in the 'ThisWorkbook' module of the sheet in qusetion and it will run automatically when the workbook is opened. Private Sub Workbook_Open() ActiveCell.Offset(0, 1) = Now() End Sub HTH, TK "JimmyJam75" wrote: Hello, I have a worksheet that I'd like to apply the current date and time (adjacent cells) in the next blank row auotmatically when the worksheet is opened. i.e. if row 10 is blank on open, then A10 will have the date applied and A11 the time. Both values must remain static. I've started by trying to write a macro that will insert the current date value into the active cell. See code below: Dim james_stamp As Date Dim james_range As Range james_stamp = Now() james_range = ActiveCell.Range Range("james_range").Value = james_stamp I'm having problems capturing the active cell and then using this value within the 'Range' function. My next challenge was replacing the active cell value with the 'next blank cell'. Then I'd need to run this macro on opening the worksheet. Can anyone push me in the right direction? Many thanks, JJ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to add todays date (static) to the current active cell usi
OK
Cells A2 and B2 update on initial run but I cannot get the macro to increment the subsequent rows (i.e, A3:B3 - A4:B4). No error is occuring, it's just not updating the sheet. Here is my code Private Sub Workbook_Open() With Sheets("Sheet1") If .Range("A2") = "" Then .Range("A2") = Format(Now(), "MM/DD/YY") .Range("B2") = Format(Now(), "h:mm:ss") Else .Range("A2").End(xlDown).Offset(0, 1) = Format(Now(), "MM/DD/YY") .Range("B2").End(xlDown).Offset(0, 1) = Format(Now(), "h:mm:ss") End If End With End Sub Any ideas? Thanks "T Kirtley" wrote: Oops, I think I misread your post...better get new reading glasses. The following change should take care of finding the first blank row in column A of "Sheet1". I also changed to create two cells, one for the date and on for the time: Private Sub Workbook_Open() With Sheets("Sheet1") If .Range("A1") = "" Then .Range("A1") = Format(Now(), "MM/DD/YY") .Range("A2") = Format(Now(), "h:mm:ss") Else .Range("A1").End(xlDown).Offset(1, 0) = Format(Now(), "MM/DD/YY") .Range("A1").End(xlDown).Offset(1, 0) = Format(Now(), "h:mm:ss") End If End With End Sub You can modify the date/time formats as needed with any date or time format that Excel recognizes. HTH, TK "T Kirtley" wrote: Something like the following should do it. Note that this should be saved in the 'ThisWorkbook' module of the sheet in qusetion and it will run automatically when the workbook is opened. Private Sub Workbook_Open() ActiveCell.Offset(0, 1) = Now() End Sub HTH, TK "JimmyJam75" wrote: Hello, I have a worksheet that I'd like to apply the current date and time (adjacent cells) in the next blank row auotmatically when the worksheet is opened. i.e. if row 10 is blank on open, then A10 will have the date applied and A11 the time. Both values must remain static. I've started by trying to write a macro that will insert the current date value into the active cell. See code below: Dim james_stamp As Date Dim james_range As Range james_stamp = Now() james_range = ActiveCell.Range Range("james_range").Value = james_stamp I'm having problems capturing the active cell and then using this value within the 'Range' function. My next challenge was replacing the active cell value with the 'next blank cell'. Then I'd need to run this macro on opening the worksheet. Can anyone push me in the right direction? Many thanks, JJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current date and time when different cell = X | Excel Worksheet Functions | |||
link cell in excel to current date | Excel Discussion (Misc queries) | |||
WHAT FUNCTION 2 ENTER SO THE CELL TURNS RED AFTER CURRENT DATE | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
How do I program a cell to automaticinput the current date as dat. | Excel Worksheet Functions |