Find data, delete row, copy in new data - multiple sheets

[gmn734]

Hi,

First post - very new to excel/vba and completely stuck here, any help is much appreciated. The basic outline is below, but having such limited knowledge of VBA im finding it v.difficult to fill in the code.

I have two sheets, DATA and OVERVIEW.

OVERVIEW sheet
>Cell D4 is what i want to search for
DATA sheet
>Find D4 in Column A
>>If found delete entire row
>>If not found then go onto next stage anyway
OVERVIEW sheet
>Copy cells D4:AS4
DATA sheet
>Paste into the first empty row in colum A


So i basically want to search for something in the data sheet, and if it is found replace it with cells D4:AS4 from the overview sheet.

Is this the best way to do it?

Thanks for any help

[AB33]

gmn74,
Welcome to the forum!
You are likely to get a speed reply if you could attach a sample book. To attach a sample, go to advance then attachment.

[gmn734]

Hi, i have attached, hopefully it works, thanks

[AB33]

gmn74,
if they match you want to copy the data in overview sheet in to Data sheet.
"If found delete entire row?"
Which one do you want to delete? Is the row from sheet overview once it is copied in to sheet Data?

[gmn734]

Hi AB33,
Not quite - If D4(Overview sheet) is found in ColumnA:A (Data sheet) I want it to delete that row in columnA:A (data sheet). Thats because i then want it to copy D4:AS4(Overview sheet) into the next empty row in columnA:A(Data sheet).

Im not sure this is the best way, but i essentially want to edit the numbers in E4:AS4(overview sheet) and then copy them back into the data sheet, removing the old data in the data sheet (if there is already old data there, if not i want it just copy the new stuff in). Cell D4(overview sheet) will be the unique identifier.

Does that clarify? Or have i just confused you even more?

[oeldere]

Seeing your file.

Not exact understand what your trying to do.

I think you should re-arange the file from columns into row (verticaly).

Then you will be able to use pivot table to analize your data.

[AB33]

No, it is not clear!
A2 of sheet data UKBIOREPR_0 is the only row which match with D4 of sheet overview. You want to delete the entire Row 2 of Sheet data and then what?
You want to copy D4-AS4 from sheet overview in to where in sheet data?

[gmn734]

No, it is not clear!
A2 of sheet data UKBIOREPR_0 is the only row which match with D4 of sheet overview. You want to delete the entire Row 2 of Sheet data and then what? Copy D4-AS4 from OVERVIEW to the next empty row in DATA (row5 once row2 has been deleted). Effectively replacing the old UKBIOREPR_0 (in the DATA sheet) with the UKBIOREPR_0 from the OVERVIEW sheet
You want to copy D4-AS4 from sheet overview in to where in sheet data?

I have amended the spreadsheet slightly. This is how it will look at the first stage. D4:AS4(OVERVIEW sheet) is the same as row2 (DATA sheet), D6:AS6(OVERVIEW sheet) is the same as row3 (DATA sheet) and so on. There will be a number of graphs in the OVERVIEW sheet, i will analyse the data and manually make changes to the numbers (E4:AS4, E6:AS6 and so on) in the OVERVIEW sheet. Then i want to use a macro to copy the updated numbers from the OVERVIEW sheet to the DATA sheet, i can then export this sheet out. In terms of process I thought the best way to do it would be to delete the old UKBIOREPR_0 in the DATA sheet and then copy the updated UKBIOREPR_0 (D4:AS4) from the OVERVIEW sheet.

Thanks for your patience