Previous Topic Next Topic

Copy row from sheet 1 to sheet 2 with formula

Closed
hadeel - Aug 18, 2015 at 07:07 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - 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.



Related:

4 responses

Answer 1 / 4
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
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
Answer 2 / 4
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
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
Answer 3 / 4
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
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
Answer 4 / 4
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
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