Search worksheet for next blank cell in column B and copy data in row from another workshe

[SierraKilo78]

My head is all spins. have tried various methods but just cant get what im looking for. Would greatly appreciate if any help rendered.

I have a workbook with 2 worksheet. "Authorization" sheet is the one in which data is entered by a user, it has 2 buttons that run a macro, "Accept" and "Reject". The other worksheet is a kind of database called "Header" which has a row 1 with the headings and in column A are predefined serial numbers "P001-P008". The purpose is to maintain statistics.

What I want to do is when the user clicks the Accept button on Authorization sheet, it should search Column B range B4:B11 in Header sheet (since column A already had data which must not change), for next empty cell and then start copying the data from different cells in Authorization to the relevant columns in Header sheet as per row headings.

E.G, search Header sheet for next empty cell between column B4:b:11, select that row and copy data from Authorization sheet cell B8 to Header sheet B column in that empty row detected, copy Authorization sheet C8 to header sheet C in the same empty row detected.

I don't know if I make sense or if its any possible even. but would appreciate help as mentioned earlier.

i'm attaching the structure of the workbook and the code im working on. let me mention the codes is a jumble of several stuff that I tried but failed to get what I want. that's while you will find much of it commented. but NONE worked . please help

Please Login or Register  to view this content.

[stnkynts]

Try this for the Accept button. There was no correlation for the "status" field

Please Login or Register  to view this content.

[Mumps1]

Where does the 'Status' in the "Header" sheet come from? Also, can I assume that you choose an 'Approver' from a drop down list in cell C20 in the "Authorization" sheet?

[SierraKilo78]

My apologies. The Column heading "Status" is instead "Agreed By". And correct cell C20 of Authorization is to be filled in Approver column of Header.

Also, the authorization form may have upto 4 recordz at a time.

Thanx a zil for all ur attention

[SierraKilo78]

thanx a ton stnkynts. that worked like a charm. BUT one problem. As you may have noticed there are 4 row of records on the Authorization sheet and hence the user can input upto 4 records at a time. so when I edited the ws1 range as:

Please Login or Register  to view this content.

it is copying all the range to the sheet but the Approver (C20 of Authorization) gets copied only in the first record that gets updated in the Header sheet. What needs to be done is say e.g there are 3 records in Authorization then, which get updated in the Header sheet, then all those 3 records should have the same name of the approver copied as well since it would be the same approver approving all 3.

[stnkynts]

Please Login or Register  to view this content.

[SierraKilo78]

This is great. I can only thank less. no intention of being nasty but just one last question. if its possible.

at the end the process a messagebox could pop that displays the pre-feed reference number in column A of the Header sheet corresponding to only the new records that were updated.

[SierraKilo78]

Thanks. I figured it out. the intersect method. Again many thanks for all you valuable help.