Automatically move down to the next row
 

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.

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


"Pank Mehta" wrote in message
...
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.

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

"Mangesh" wrote:

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


"Pank Mehta" wrote in message
...
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.

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


--
HTH

Bob Phillips

"Pank Mehta" wrote in message
...
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

"Mangesh" wrote:

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


"Pank Mehta" wrote in message
...
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.

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



"Pank Mehta" wrote in message
...
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

"Mangesh" wrote:

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


"Pank Mehta" wrote in message
...
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.

Mangesh, Bob,

Many thanks for the prompt response and the solution.

"Mangesh" wrote:

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



"Pank Mehta" wrote in message
...
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

"Mangesh" wrote:

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


"Pank Mehta" wrote in message
...
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.