Move record from worksheet B to A based on a condition

Hello,

I would like to have a way that automatically transfers record from worksheet B to worksheet A based on a condition. Please see the attached.

In worksheet "secondary", the logic is:

Whenever a record has a diagnosis of "stroke", i want that record to be moving to worksheet "Main". After record has moved to worksheet "Main", I want that record in worksheet "secondary" to be deleted, or fields turn to null.

So for the example in the attached workbook, record 634 from "secondary" would then move below record 234 in "Main". (always add below the previous)

Can this be done more automatically without having users delete rows, and then copy and paste that record to "Main"? By the way, more and more records will be added in regularly, but this is just to give you an idea.

Thank you

hi Lifeseeker, please check attachment, the code will move records from Sheet "secondary" as soon as you will activate main sheet. Will that be acceptable?

if nothing happens on sheet "main" activation run the following code beforehand:

Please Login or Register  to view this content.

Originally Posted by watersev

hi Lifeseeker, please check attachment, the code will move records from Sheet "secondary" as soon as you will activate main sheet. Will that be acceptable?

if nothing happens on sheet "main" activation run the following code beforehand:

Please Login or Register  to view this content.

Hi there,

It is partially working. While I see that the record does get copied, it does not actually get pasted to "Main" sheet when I activate the main sheet.

Also, stroke records in "secondary" sheet don't get deleted. The idea here is just to transfer records from "secondary" to "main".

Please see the code attached.

Please Login or Register  to view this content.

Thanks

I opened your attached file in post #3. I put data in A:C columns where C column cell value is stroke. I activate Main sheet the entered data with stroke moves to main sheet and "stroke" rows get deleted on Sheet secondary. Isn't it what you asked for? Do I miss anything? I do not see any problems, everything is per your request.

PS. Application.EnableEvents lines are unnecessary in the code above.

Originally Posted by watersev

I opened your attached file in post #3. I put data in A:C columns where C column cell value is stroke. I activate Main sheet the entered data with stroke moves to main sheet and "stroke" rows get deleted on Sheet secondary. Isn't it what you asked for? Do I miss anything? I do not see any problems, everything is per your request.

PS. Application.EnableEvents lines are unnecessary in the code above.

Hi Watersev,

Yes, it works out perfectly. But interestingly, when I was testing it, it wasn't pasting the 2nd time around. Anyway, I have a minor request:

A bit of twist perhaps:

I want Excel to filter on two words: "stroke" and "minor". The code now becomes:

Please Login or Register  to view this content.

When I run it, I get a data type mismatch error. I think the code shouldn't read .AutoFilter 3, "stroke" Or "Minor"?

Can you point out the correct way?

Thanks

Please Login or Register  to view this content.

Hi Watersev,

I cannot seem to fully understand the .offset().resize() method in the code. I have tried to not use the .resize, and while I did see a difference in the results generated, I couldn't understand how it works.

Could you explain to me a bit more?

Please Login or Register  to view this content.

Thanks

Please Login or Register  to view this content.

Anyone please?

Could you help with the subsequent post please?

Thanks

Please Login or Register  to view this content.

Hi watersev,

I am running into problems, perhaps it is because I'm not fully understanding the offset and resize. Please see the attached.

I would like to do the same thing, but this time in "Secondary", the very first two columns are not the same as the col A and col B in "Main"

I want ID to ID, Name to Name, BP to BP and HH to HH.

I'm using this code, but I"m struggling.....also, what does cells(lrow,2) mean? Copy the selected range to the 2nd column or copy the selected range to two columns?

Please Login or Register  to view this content.

Could you help?

Thanks