Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Move cell info and info in neighboring cell on new entry
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 71
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,533
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 71
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,533
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 71
Default 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




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,533
Default 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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 71
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Move cell info on new entry belvy123 Excel Discussion (Misc queries) 8 April 28th 07 03:14 PM
Automaticall pick up info from a cell from a shet t oanither sheet , in the same cell, same book [email protected] Excel Worksheet Functions 6 December 22nd 06 03:39 AM
Link info in one cell to info in several cells in another column (like a database) hansdiddy Excel Discussion (Misc queries) 1 February 22nd 06 02:27 AM
How to create/run "cell A equals Cell B put Cell C info in Cell D abmb161 Excel Discussion (Misc queries) 5 January 26th 06 06:36 PM
how do i get excel to see info in one cell, look at info in anoth. ditto Excel Discussion (Misc queries) 3 February 1st 05 04:37 PM


All times are GMT +1. The time now is 02:18 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement