Copying from one sheet to another with complication

Hi,

I am hoping this will be a nice easy fix but I don't have the knowledge to complete it.

I have a form which I would like users to update and then be able to click a button and update a database. The form is on Sheet 1 cells B4 to Z5 (First row headers, second row data) and I would like when they press the button the macro would look up the value in cell E5 in column C of the other sheet (Sheet 2) and find where it is present. Then it would copy the entire data from Sheet 1 and replace the whole line on Sheet 2 (i.e. Sheet 1 B5 - Z5 into Sheet 2 A(whatever row the value in cell E5 of sheet 1 relates to) to Z (whatever row the value in cell E5 of sheet 1 relates to)

Hopefully makes sense and I can sort of follow some of the stages but hoping someone can just show me the code I would need to achieve the above?

Many thanks,
Tony

Could we see the workbook?

BSB

Hi BSB,

Sorry for the delay - here is a version of the file

Maybe:

Please Login or Register  to view this content.

Thanks John, this almost works exactly as I need - my only extra bit is there are going to be some cells that have formula in them that I would like to copy over as well.

When I use this macro is seems to just copy absolute numbers instead of formula?

Try:

Please Login or Register  to view this content.

Hi John,

Thank you for this it has worked exactly as I was hoping.

Forgive me for asking lots of questions but I have an extra requirement that I'm hoping you can help with? This process works fine for when I want to edit an existing record but sometimes we want to add new records as well. I am going to have a separate form for people to add new records (mainly because this is easier for the teams to understand more than anything)

In the new form all the structure would still be the same except instead of finding the existing jobs number as the code does below I would just like it to find the first empty row on Sheet 2 and copy all the information over from 'Sheet3' for want of a better sheet name. I'm struggling at finding a way to get Excel to select the next available row on Sheet 2 to copy the data.

Thanks in advance for any help you can give, this is really helping me learn how it all works together.

Tony

This will take you to the next empty row based on Column "A" in Sheet2 if that's what you mean.

Please Login or Register  to view this content.

John,

Thanks for the above I'm still struggling somewhat though. Maybe I didn't explain myself very well.

Basically the code you have above works perfectly, when there is a record already that it can find and overwrite. What I would like it for the copy part to work just as well and it just to copy to the bottom of Sheet 2.

I am by no means an expert at this so struggling to understand which part of your code relates to the copying and which part relates to the destination for instance.

So from above I would like to copy everything from Sheet 1 B4 to Z5 into the next available (empty) row on Sheet 2

Hi John,

I am certain it is my fault - been a very long day!

I'm not sure that covers what I need, I don't need to look up the E value this time as it is a completely new record, it will purely go and add to the bottom of the database on the next available line (I.e. copy everything from the row (73) onto the next available row on the database)

Is that remotely clearer?

Try:

Please Login or Register  to view this content.

Thank you John - works like a charm.

You have been more than helpful!!!!

You're welcome. Glad to help out and thanks for the feedback.