Copy row from sheet 1 to sheet 2 with formula

[Closed]
Report
-
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
-
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

Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
238
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
238
Hello Mazzaropi,

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

Cheerio,
vcoolio.
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
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