Automatically move down to the next row

P

Pank Mehta

I have a macro that automatically inserts the time and date in certain
columns.

Is there a way as part of the macro once the date and time have been
populated, I can get to the start of the next row?

Any help appreciated.
 
M

Mangesh

You know the row in which you are adding the time/date. Just add 1 to it and
select the first column.
if you show your macro, it would be easier to help.

- Mangesh
 
P

Pank Mehta

The macro is as follows:-

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect
With .Offset(0, 1)
.Value = Date
.NumberFormat = "dd mmm yy"
End With
With .Offset(0, 2)
.Value = Now
.NumberFormat = "hh:mm"
End With
End If
End With

ws_exit:
Application.EnableEvents = True
Me.Protect

End Sub
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect
With .Offset(0, 1)
.Value = Date
.NumberFormat = "dd mmm yy"
End With
With .Offset(0, 2)
.Value = Now
.NumberFormat = "hh:mm"
End With
Cells(.Row+1,1).Activate
End If
End With

ws_exit:
Application.EnableEvents = True
Me.Protect

End Sub
 
M

Mangesh

Add the line:
Cells(Target.Row + 1, 1).Select



Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect
With .Offset(0, 1)
.Value = Date
.NumberFormat = "dd mmm yy"
End With
With .Offset(0, 2)
.Value = Now
.NumberFormat = "hh:mm"
End With
Cells(Target.Row + 1, 1).Select ' new line added
End If
End With

ws_exit:
Application.EnableEvents = True
Me.Protect

End Sub


- Mangesh
 
Top