Copy row from sheet 1 to sheet 2 with formula

Closed
hadeel - Aug 18, 2015 at 07:07 AM
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 - Aug 18, 2015 at 10:34 AM
Hello,

i need to copy a row from one sheet to another with formula and keep updating the data in sheet 2 every time i change the data in sheet 1
i tried "equal" (=) but i need something faster.



4 replies

Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Aug 18, 2015 at 09:11 AM
hadeel, Good morning.

You're using the most basic function available at Excel.
The fastest function.

Probably the question is not a formula but the size and/or complexity of your Excel file.

Verify if you really want to duplicate that full line each time you updates any cell at the origin line.
1
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Aug 18, 2015 at 09:18 AM
Hello Hadeel,

A Worksheet_Change event may be what you need:-


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Column = 11 Then
tRow = Target.Row
Range("K" & tRow).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If

Application.EnableEvents = True
Sheet2.Select

End Sub


I've had to make some assumptions as to what you are trying to do. I'm assuming that you have one column in Sheet 1 (Column K in the code above) where you make your data changes. As long as you make the change in Column K last when entering your data and then click away, the whole row of relevant data will be transferred to Sheet 2.

Place the code in the Sheet 1 module by right clicking on the Sheet 1 tab, selecting "view code" and in the big white field that appears, pasting the above code.

You may have to change the cell references in the code to suit yourself.

You can peruse my test work book at the following link to see if it is close to what you are trying to do:-

https://www.dropbox.com/s/xrba0sgfm1qyz5x/Hadeel.xlsm?dl=0

Enter any value in Column K in Sheet 1 then click away to execute the code.

I hope that this helps.

Cheerio,
vcoolio.
1
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Aug 18, 2015 at 09:20 AM
Hello Mazzaropi,

Apologies. We must have posted at the same time. I'll leave it with you,

Cheerio,
vcoolio.
0
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Aug 18, 2015 at 10:34 AM
Hello vcoolio, Good morning.

You're always welcome.

We are in a collaborative space.
Than as many opinions as possible enriches the post.
I don't know VBA, you gave to the user a very good contribution.

Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0