ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Move cell info and info in neighboring cell on new entry (https://www.excelbanter.com/excel-discussion-misc-queries/192521-move-cell-info-info-neighboring-cell-new-entry.html)

belvy123
Move cell info and info in neighboring cell on new entry
 
Hi All

I have a sheet that I use the following script to move info on a new entry.
It was written by Bob Phillips
I want to be able to use this script and add to it to do the following.

I want to enter a date in B6 and then in C6 enter a Number for a part. When
I hit enter after entering the number in C6 I want to have the script move
the info in both B6 and C6 down 1 Row each time a new entry is made.
Thanks for the help

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String=
"B6,B15,B24,B33,E6,E15,E24,E33,H6,H15,H24,H33,K6,K 15,K24,K33,N6,N15,N24,N33,Q6,Q15,Q24,Q33" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(4).Copy .Offset(1, 0)
.Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
--
crunchin numbers

Per Jessen
Move cell info and info in neighboring cell on new entry
 
Hi

This will move data in B6:C6 and previously entered data below down one row.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C6" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
tRows = Range("B5").End(xlDown).Row - 1
With Target
.Offset(0, -1).Resize(tRows, 2).Copy .Offset(1, -1)
Range("B6:C6").Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Regards,
Per

"belvy123" skrev i meddelelsen
...
Hi All

I have a sheet that I use the following script to move info on a new
entry.
It was written by Bob Phillips
I want to be able to use this script and add to it to do the following.

I want to enter a date in B6 and then in C6 enter a Number for a part.
When
I hit enter after entering the number in C6 I want to have the script move
the info in both B6 and C6 down 1 Row each time a new entry is made.
Thanks for the help

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String=
"B6,B15,B24,B33,E6,E15,E24,E33,H6,H15,H24,H33,K6,K 15,K24,K33,N6,N15,N24,N33,Q6,Q15,Q24,Q33"
'<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(4).Copy .Offset(1, 0)
.Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
--
crunchin numbers


belvy123
Move cell info and info in neighboring cell on new entry
 
Hi again

Your script works however when going to enter another set of new data in B6
and C6 it does not move the new data down to the next row.
Also how would I modify this script to work in several cells
Say for instance
b6 c6 b15 c16 b24 c24 etc etc etc

Thanks for the help
--
cruchnin numbers


"Per Jessen" wrote:

Hi

This will move data in B6:C6 and previously entered data below down one row.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C6" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
tRows = Range("B5").End(xlDown).Row - 1
With Target
.Offset(0, -1).Resize(tRows, 2).Copy .Offset(1, -1)
Range("B6:C6").Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Regards,
Per

"belvy123" skrev i meddelelsen
...
Hi All

I have a sheet that I use the following script to move info on a new
entry.
It was written by Bob Phillips
I want to be able to use this script and add to it to do the following.

I want to enter a date in B6 and then in C6 enter a Number for a part.
When
I hit enter after entering the number in C6 I want to have the script move
the info in both B6 and C6 down 1 Row each time a new entry is made.
Thanks for the help

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String=
"B6,B15,B24,B33,E6,E15,E24,E33,H6,H15,H24,H33,K6,K 15,K24,K33,N6,N15,N24,N33,Q6,Q15,Q24,Q33"
'<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(4).Copy .Offset(1, 0)
.Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
--
crunchin numbers



Per Jessen
Move cell info and info in neighboring cell on new entry
 
Hi again

This will move sets of data as specified in WS_Range, to next empty row.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C6,C16,C24" '<== change to suit
'On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
tRows = Range("B5").End(xlDown).Row - 1
With Target
If .Offset(1, 0).Value = "" Then
tRow = .Offset(-1, 0).End(xlDown).Row + 1
Else
tRow = .End(xlDown).Row + 1
End If
.Offset(0, -1).Resize(1, 2).Copy Cells(tRow, .Column - 1)
.Offset(0, -1).Resize(1, 2).Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Regards,
Per

"belvy123" skrev i meddelelsen
...
Hi again

Your script works however when going to enter another set of new data in
B6
and C6 it does not move the new data down to the next row.
Also how would I modify this script to work in several cells
Say for instance
b6 c6 b15 c16 b24 c24 etc etc etc

Thanks for the help
--
cruchnin numbers


"Per Jessen" wrote:

Hi

This will move data in B6:C6 and previously entered data below down one
row.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C6" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
tRows = Range("B5").End(xlDown).Row - 1
With Target
.Offset(0, -1).Resize(tRows, 2).Copy .Offset(1, -1)
Range("B6:C6").Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Regards,
Per

"belvy123" skrev i meddelelsen
...
Hi All

I have a sheet that I use the following script to move info on a new
entry.
It was written by Bob Phillips
I want to be able to use this script and add to it to do the following.

I want to enter a date in B6 and then in C6 enter a Number for a part.
When
I hit enter after entering the number in C6 I want to have the script
move
the info in both B6 and C6 down 1 Row each time a new entry is made.
Thanks for the help

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String=
"B6,B15,B24,B33,E6,E15,E24,E33,H6,H15,H24,H33,K6,K 15,K24,K33,N6,N15,N24,N33,Q6,Q15,Q24,Q33"
'<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(4).Copy .Offset(1, 0)
.Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
--
crunchin numbers

belvy123
Move cell info and info in neighboring cell on new entry
 
Hi Again

the script is almost there

Here is the end result I would like to see


original list

B C
6
7 5/5/08 8
8 4/4/08 9
9 3/3/08 10
10 2/2/08 11




new entry into b6 and c6 after entering data allrows move down one and the
latest date is deleted so there is a total of 4 dates shown leaving b6 and c6
empty to enter next data.
I hope this is clear enough. I really appreciate your help and patience. the
last script was reversing the order of dates with the oldest on top and the
newest on ther bottom

Thanks

Dan N
b c
6
7 6/6//08 7
8 5/5/08 8
9 4/4/08 9
10 3/3/08 10


"Per Jessen" wrote:

Hi again

This will move sets of data as specified in WS_Range, to next empty row.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C6,C16,C24" '<== change to suit
'On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
tRows = Range("B5").End(xlDown).Row - 1
With Target
If .Offset(1, 0).Value = "" Then
tRow = .Offset(-1, 0).End(xlDown).Row + 1
Else
tRow = .End(xlDown).Row + 1
End If
.Offset(0, -1).Resize(1, 2).Copy Cells(tRow, .Column - 1)
.Offset(0, -1).Resize(1, 2).Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Regards,
Per

"belvy123" skrev i meddelelsen
...
Hi again

Your script works however when going to enter another set of new data in
B6
and C6 it does not move the new data down to the next row.
Also how would I modify this script to work in several cells
Say for instance
b6 c6 b15 c16 b24 c24 etc etc etc

Thanks for the help
--
cruchnin numbers


"Per Jessen" wrote:

Hi

This will move data in B6:C6 and previously entered data below down one
row.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C6" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
tRows = Range("B5").End(xlDown).Row - 1
With Target
.Offset(0, -1).Resize(tRows, 2).Copy .Offset(1, -1)
Range("B6:C6").Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Regards,
Per

"belvy123" skrev i meddelelsen
...
Hi All

I have a sheet that I use the following script to move info on a new
entry.
It was written by Bob Phillips
I want to be able to use this script and add to it to do the following.

I want to enter a date in B6 and then in C6 enter a Number for a part.
When
I hit enter after entering the number in C6 I want to have the script
move
the info in both B6 and C6 down 1 Row each time a new entry is made.
Thanks for the help

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String=
"B6,B15,B24,B33,E6,E15,E24,E33,H6,H15,H24,H33,K6,K 15,K24,K33,N6,N15,N24,N33,Q6,Q15,Q24,Q33"
'<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(4).Copy .Offset(1, 0)
.Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
--
crunchin numbers

Per Jessen
Move cell info and info in neighboring cell on new entry
 
Hi Dan

I think this covers all your requirements :-)

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C6,C16,C24" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
tRow = .End(xlDown).Row - .Row + 1
If tRow 4 Then tRow = 4
.Offset(0, -1).Resize(tRow, 2).Copy .Offset(1, -1)
.Offset(0, -1).Resize(1, 2).Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Best regards
Per

"belvy123" skrev i meddelelsen
...
Hi Again

the script is almost there

Here is the end result I would like to see


original list

B C
6
7 5/5/08 8
8 4/4/08 9
9 3/3/08 10
10 2/2/08 11




new entry into b6 and c6 after entering data allrows move down one and the
latest date is deleted so there is a total of 4 dates shown leaving b6 and
c6
empty to enter next data.
I hope this is clear enough. I really appreciate your help and patience.
the
last script was reversing the order of dates with the oldest on top and
the
newest on ther bottom

Thanks

Dan N
b c
6
7 6/6//08 7
8 5/5/08 8
9 4/4/08 9
10 3/3/08 10

belvy123
Move cell info and info in neighboring cell on new entry
 
Hi Per

Thankyou Oh so much. it works fantasticly
You are a life saver

Thanks again

Dan N
--
cruchnin numbers


"Per Jessen" wrote:

Hi Dan

I think this covers all your requirements :-)

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C6,C16,C24" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
tRow = .End(xlDown).Row - .Row + 1
If tRow 4 Then tRow = 4
.Offset(0, -1).Resize(tRow, 2).Copy .Offset(1, -1)
.Offset(0, -1).Resize(1, 2).Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Best regards
Per

"belvy123" skrev i meddelelsen
...
Hi Again

the script is almost there

Here is the end result I would like to see


original list

B C
6
7 5/5/08 8
8 4/4/08 9
9 3/3/08 10
10 2/2/08 11




new entry into b6 and c6 after entering data allrows move down one and the
latest date is deleted so there is a total of 4 dates shown leaving b6 and
c6
empty to enter next data.
I hope this is clear enough. I really appreciate your help and patience.
the
last script was reversing the order of dates with the oldest on top and
the
newest on ther bottom

Thanks

Dan N
b c
6
7 6/6//08 7
8 5/5/08 8
9 4/4/08 9
10 3/3/08 10


All times are GMT +1. The time now is 08:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com