Copy entire row to another worksheet

[Carl_London]

I am new to excel and using macros so please be kind!

I wish to copy an entire row from one worksheet called "list" and copy that entire row to another worksheet called "logged". The copied row should go to the next available empty row in the "logged" worksheet and then clear the contents of that row from the original worksheet. I would like to be able to do the move by using a macro button.

Can anyone help please?

Thanks, Carl

This code works but not the way I want, in this version I have to enter an "l" in the coloumn A of the first worksheet, but I'd rather do it with one mouse click of a button:

Private Sub Worksheet_Change(ByVal Target As Range)

' Check to see only one cell updated If Target.CountLarge > 1 Then Exit Sub

' Check to see if entry is made in column B after row 5 and is set to "Yes" If Target.Column = 1 And Target.Row > 4 And Target.Value = "l" Then Application.EnableEvents = False ' Copy columns B to M to complete sheet in next available row Range(Cells(Target.Row, "B"), Cells(Target.Row, "N")).Copy Sheets("logged").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) ' Delete current row after copied Rows(Target.Row).ClearContents Application.EnableEvents = True

 

[offthelip]

welcome to the forum, I think the way I would program that functionality is to put the code into the double click event and then trigger it only when doulbe clicking column A ( you could easily change the column)
Try this code in the worksheet code for the "list" workhseet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
rowno = Target.Row
If Target.Column = 1 Then
  lastcol = Cells(rowno, Columns.Count).End(xlToLeft).Column
  inarr = Range(Cells(rowno, 1), Cells(rowno, lastcol))
  Range(Cells(rowno, 1), Cells(rowno, lastcol)) = ""
  
  With Worksheets("logged")
   lastrow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
   .Range(.Cells(lastrow, 1), .Cells(lastrow, lastcol)) = inarr
   End With
End If
End Sub

Now just doulbe click on a a row in column A and check it copies across ok

 

[Carl_London]

Hi,

Thank you for your help. Unfortunately it returned an error and did not copy the row across (although it did disappear from the list worksheet!)

 

[Carl_London]

Oops, sorry that error was because I messed up with the name of the logged sheet. No error message now but it's only deleting from the first worksheet, not moving to the logged worksheet.
Thanks again for your help (and patience!)

 

[Skyybot]

Try this small change in your original post.

**Quoted**
Check to see if entry is made in column B after row 5 and is set to "Yes" If Target.Column = 1 And Target.Row > 4 Then Application.EnableEvents = False ' Copy columns B to M to complete sheet in next available row Range(Cells(Target.Row, "B"), Cells(Target.Row, "N")).Copy Sheets("logged").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) ' Delete current row after copied Rows(Target.Row).ClearContents Application.EnableEvents = True
**Quoted**

 

[Skyybot]

Try this small change in your original post.

**Quoted**
Check to see if entry is made in column B after row 5 and is set to "Yes" If Target.Column = 1 And Target.Row > 4 Then Application.EnableEvents = False ' Copy columns B to M to complete sheet in next available row Range(Cells(Target.Row, "B"), Cells(Target.Row, "N")).Copy Sheets("logged").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) ' Delete current row after copied Rows(Target.Row).ClearContents Application.EnableEvents = True
**Quoted**

I removed the And Target.Value = "I"