Previous Topic Next Topic

Transferring Selected rows to Another location on anothersheet

Closed
jswilson Posts 3 Registration date Monday June 24, 2013 Status Member Last seen July 3, 2013 - Jun 24, 2013 at 07:29 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 4, 2013 at 10:51 AM
I Am trying to Move a selected range of cells to another sheet.
As an example
"sheet1" Columns C,D,E,F,G,has a data list entered and when i enter the "name" (this will be the same name of the sheet)in column H, I want that data be transferred to the "name" sheet And they will be be mutiple :name" sheets. I am brand new to this and have no code writing experience, I don't know whether i can use a macro or have to do VB. Anyone out there brave enough to try to help out?

Thanks
Jay
Related:

4 responses

Answer 1 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 2, 2013 at 10:32 AM
Hi Jswilson,

Once implemented the code will do the following:
When you make a change to a cell in column H then column C:G of the respective row will be copied and pasted to the first available row in the sheet mentioned in column H. Note that when a value is entered in column H that does not exist as a sheet you will get an error message (We can handle that differently if desired).

Here is the code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
Range(Cells(Target.Row, "C"), Cells(Target.Row, "G")).Copy _
Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub

To implement the code, right-click on the sheets (sheet1) tab and select view code. Now paste the code in the big white field that appears in a new window. That new window (Microsoft Visual Basic) does not need to be open for the code to work.

Let me know how things work out and if the code need some tweeks.

Since you said you are new to macro, bare in mind that the blue arrows (undo) does not work. So either make a backup or reload your file when you expected something else.

Best regards,
Trowa
1
Answer 2 / 4
jswilson Posts 3 Registration date Monday June 24, 2013 Status Member Last seen July 3, 2013
Jul 2, 2013 at 07:01 PM
this works great except for one thing after the 6th entry the 7th entry row is getting copied with the latest entry assigned, can not get more than 7 entries ao any given sheet. any ideas?


thank you Jay
0
Answer 3 / 4
jswilson Posts 3 Registration date Monday June 24, 2013 Status Member Last seen July 3, 2013
Jul 3, 2013 at 01:05 PM
I Just Figured this problem out If the 1st column was blank it would just copy the latest entry. thank you so much this works great now i know this.
0
Answer 4 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 4, 2013 at 10:51 AM
Hi Jay,

You are right, column A is used to determine if the row is empty. We could change this if desired.

Best regards,
Trowa
0