Copy row from sheet 1 to sheet 2 with formula

Closed
-
Posts
1950
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
January 9, 2022
-
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
1950
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
January 9, 2022
148
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.
Posts
1334
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 28, 2022
243
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.
Posts
1334
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 28, 2022
243
Hello Mazzaropi,

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

Cheerio,
vcoolio.
Posts
1950
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
January 9, 2022
148
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