automatic copy of a choised row in another sheet

milt2004

New Member
Joined
Sep 4, 2004
Messages
28
Hello to the Board.


Is there the possibility to copy automatically a row from a sheet to another, of the same workbook?

For better explain, I write a number in a cell (the first or the second of every row) and after I leave it, automatically the row, where it is, is copied in another sheet.

It is possible this?
Many thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hi Milt,
Since you didn't indicate where in the other sheet you'd like the copied row to be pasted, I told it to start in row 1 and paste to the next row each time.
This will automatically copy the entire row to sheet2 each time there is a change in column B. Is that pretty much what you wanted?
This goes into the sheet module for the sheet you want to copy from.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Column <> 2 Then Exit Sub
If Target = "" Or Target.Cells.Count > 1 Then Exit Sub
i = Target.Row
If Sheets("Sheet2").Cells(1, 1) = "" Then
    Cells(i, 1).EntireRow.Copy Sheets("Sheet2").Cells(1, 1)
    Else: Cells(i, 1).EntireRow.Copy Sheets("Sheet2").Cells(65536, 1).End(xlUp)(2, 1)
End If
End Sub
Hope it helps,
Dan
 

milt2004

New Member
Joined
Sep 4, 2004
Messages
28
Thank you very much Dan.

The code seems to work. But it copy only one row (every last I choose).
I desire to copy every row I choose from the second cell of it.

It is possible?
Thanks
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
If I understand what you're looking for, this will work, but it's not an auto executing macro like the other one. It goes into a standard module and will need to be called from a button or some other means.
Code:
Sub CopyAllOfThis()
If Sheets("Sheet2").Cells(1, 1) = "" Then
    Selection.Rows.EntireRow.Copy Sheets("Sheet2").Cells(1, 1)
    Else: Selection.Rows.EntireRow.Copy Sheets("Sheet2").Cells(65536, 1).End(xlUp)(2, 1)
End If
End Sub
Is that what you wanted?
Dan
 

milt2004

New Member
Joined
Sep 4, 2004
Messages
28
Hi Dan.

The first Code I think is more better. I wish to send a little part of my workbook for more clear explain. But I can't.

This is my email: myoiaku@yahoo.it
If you want to send to me your mail I can send to you the two worksheet about I want to apply the code. I think is better for you to understand the way the code have to work.
Many thanks.
 

Forum statistics

Threads
1,148,393
Messages
5,746,441
Members
424,019
Latest member
dpteo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top