Transferring Selected rows to Another location on anothersheet

Closed
Report
Posts
3
Registration date
Monday June 24, 2013
Status
Member
Last seen
July 3, 2013
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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

4 replies

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
Posts
3
Registration date
Monday June 24, 2013
Status
Member
Last seen
July 3, 2013

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
Posts
3
Registration date
Monday June 24, 2013
Status
Member
Last seen
July 3, 2013

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
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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