Auto update rows

[md55mrd]

I use excel 2016 on win 10 pro. I have a workbook that when data is entered on one sheet it updates rows on another sheet. What I'm wondering is how to make it so that after data is entered it locks then moves to the next row and sheet 1 can have new data entered and it updates on sheet 2 in row b but keeps the data in row a.

 

[Joe4]

Welcome to the Board!

You would use VBA to do that.
Here is a some code that I came up with that shows you how to do that. It will copy the data currently entered in cells A1:C1 on Sheet1, and paste to the first available row on Sheet2.

Sub MyCopyMacro()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim myRow As Long
    
'   Specificy entry sheet
    Set ws1 = Sheets("Sheet1")
'   Specify sheet to write entries to
    Set ws2 = Sheets("Sheet2")

'   Find first blank row to place new data on (on ws2)
    myRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy data from cells A1:B1 on ws1 to ws2
    ws1.Range("A1:C1").Copy ws2.Range("A" & myRow)
    
End Sub

So you would just run the macro when you make a new entry (you could even add a button to click to your sheet to run it, if you like).

Note that there are ways to get the code to run automatically, but that can be a little tricky (to get it to only run when you want).
For example, if you are entering values in multiple cells, you would only want it to run when you have completed your entry. If you have a way of defining that, then we might be able to use automated code.

 

[md55mrd]

I must be really bad at code. I know where to go to type it in but when I do it's not working for me. Is there a way for me to attach the workbook I have so you can better see what I currently have done and am trying to figure out?

 

[md55mrd]

I must be really bad at code. I know where to go to type it in but when I do it's not working for me. Is there a way for me to attach the workbook I have so you can better see what I currently have done and am trying to figure out?

Sorry, I realized there's note that states attachments can't be added.

 

[md55mrd]

I've added the link to the spreadsheet that I currently have.
Sheet 2 gets updated weekly and the information updates to sheet 2 but I want to be able to keep the data in sheet 2, remove the data from sheet 1 and then add new data the next week and have it update on sheet 2 under the new week and so on.
I've added the link to my spreadsheet attached via dropbox. Please let me know if I've done this entire thing incorrectly or what tweaks need to be made. If code is being given to me I will probably need the exact way to do it because I am beyond a beginner with VBA.
Thanks for all the help and information.
https://www.dropbox.com/s/izrzrt2xcjgwqi7/Box Counts.xlsx?dl=0

 

[Joe4]

All file sharing sites are blocked for me (corporate security policy), so I cannot view your file. For security reasons, you will that many people here are unable (or unwilling) to download files from the internet.

There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Can you just explain the following:
- what cells you are entering information into
- what columns they should be mapped to
- what should "trigger" the data transfer (i.e. when you complete a certain cell, or something like that)

 

[mumps]

Click here to download your file. I have added a column O in your Tape Totals sheet with a drop down. After you have entered all your data in columns C to L, select "Done" in column O of that row and the other sheet will be updated automatically.