Transferring Selected rows to Another location on anothersheet Closed

Question

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

TrowaD · Answer

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

jswilson · Answer

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

jswilson · Answer

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.

TrowaD · Answer

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

Transferring Selected rows to Another location on anothersheet

4 responses

Similar discussions

Subscribe To Our Newsletter!