How to copy merged rows from a Sheet to another one

Hello everyone,
I'm working on a file which has a table composed by merged rows on Sheet1 and another table with singular cells on Sheet2.
Basically, Sheet1 works as a "source" for Sheet2, which then becomes the true database.

My question is: is it possible to make a macro which recognizes the last product in the merged table and copies it into Sheet2?
I made an example for you on the attached file.

I also made a code which lets you unmerge the rows copied and makes them become part of the database table.


I hope I expressed myself pretty well. On the other hand i'll reformulate my question.
Thank you in advance!

Possibly...

Please Login or Register  to view this content.

You guys are geniuses!
I love both your macros, even though kvsrinivasamurthy's one is slighly more precise (the "dot" doesn't become a "comma" in Sheet2).
Seriously, I didn't think this was actually possible to do!!

Thank you very much, guys. I've got a lot to learn from you!

P.S. I don't know if this breaks any rules, in case I'll edit and cut the last sentence...
I opened another thread about filtering pivot tables with combo boxes. May you help me with them too, please?

Glad to help and thanks for the rep!

Originally Posted by kvsrinivasamurthy

This code is very fast.
code:

Hello kvsrinivasamurthy and thank you for helping me again!
How are you?

Your new macro works and is indeed fast!
Unluckily it has some "problems" (it's an exaggerated word...) that your previous macro didn't have:

- if I add a hyperlink in Sheet1 (I would need hyperlinks only in column "A"), it isn't copied in Sheet2;
- now it tranforms the dots in Sheet1 in commas (column "E" in Sheet2);
- if the Notes for Managers (column "K" in Sheet1) are blank, once copied in Sheet 2 they are filled with a zero (column "F" in Sheet2).

I hope I expressed myself better, this time, even though I must admit that there's always the possibility that I made a mistake somewhere.

Edit: I forgot to mention that today I used your first macro at work and it worked in less than 5 seconds! Do you think it is related to the computer I'm using?

Originally Posted by kvsrinivasamurthy

I have revised my first code to work faster. Try this code and pl give feedback.
I suspect RAM problem in the first computer that you have tried.

Hello kvsrinivasamurthy, first of all thank you so much, it looks like your new macro works like a charm!

Your manual method is really something, too, even though I prefer your macros: much more quicker!

About my computer... curiously I never changed it!
Today your first macro still needed about 5 seconds to unmerge everything. I suspect that you're right about suspecting a RAM problem, because when I close everything apart from Excel it is indeed fast.
Next week I'll give you a better feedback, since I'll try your new macro at work.

Originally Posted by kvsrinivasamurthy

I saw there is no post from jindon.

You are right, I'm sorry for my lapsus!