+ Reply to Thread
Results 1 to 15 of 15

How to copy merged rows from a Sheet to another one

  1. #1
    Registered User
    Join Date
    01-18-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    22

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,246

    Re: How to copy merged rows from a Sheet to another one

    Possibly...
    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: How to copy merged rows from a Sheet to another one

    Code for macro
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 01-26-2021 at 12:21 PM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    01-18-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    22

    Re: How to copy merged rows from a Sheet to another one

    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?

  5. #5
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,246

    Re: How to copy merged rows from a Sheet to another one

    Glad to help and thanks for the rep!

  6. #6
    Registered User
    Join Date
    01-18-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    22

    Re: How to copy merged rows from a Sheet to another one

    Hello everyone,
    I know I already marked this thread as "Solved", but I didn't see anything about commenting a solved thread in the rules, so hopefully it should be fine... If it's not the case I'll open a new one.

    I was wondering if, with dangelor's macro, it is possible to copy hyperlinked cells too and if the "comma" can be converted into a "dot" in Sheet2.

    kvsrinivasamurthy's one is a very powerful tool and it helped me making a huge job, since it unmerges ALL the merged cells in Sheet1, keeping the hyperlinks and the dots. Its problem is that, being my table in Sheet1 huge (more than a hundred thousands rows), it's starting to get slower. Now it takes about 5 minutes to work.

    Dangelor's macro, instead, can copy just the last row of merged cells and move it to the table in Sheet2. It's a useful compromise, because it would let me save a lot of time, from now on.

    Would you help me, please?

    Thank you in advance!
    ifritaeon61
    Last edited by ifritaeon61; 02-03-2021 at 06:18 PM.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: How to copy merged rows from a Sheet to another one

    This code is very fast.
    code:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-18-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    22

    Re: How to copy merged rows from a Sheet to another one

    Quote Originally Posted by kvsrinivasamurthy View Post
    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?

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: How to copy merged rows from a Sheet to another one

    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.
    Please Login or Register  to view this content.
    Last edited by kvsrinivasamurthy; 02-05-2021 at 12:12 AM.

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: How to copy merged rows from a Sheet to another one

    Another manual fastest method.
    Copy entire data to another sheet.
    delete columns B to H
    select Data--> Filter
    select Blanks in drop down of column A.
    Now select A2 to last cell in H column.
    Right click --> delete row.
    Dialogue box Delete entire row? -->Ok
    Select A1-->select all in drop down --> Ok
    Remove the filter.
    This is the result required.
    Last edited by kvsrinivasamurthy; 02-05-2021 at 03:08 AM.

  11. #11
    Registered User
    Join Date
    01-18-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    22

    Re: How to copy merged rows from a Sheet to another one

    Quote Originally Posted by kvsrinivasamurthy View Post
    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.

  12. #12
    Registered User
    Join Date
    01-18-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    22

    Re: How to copy merged rows from a Sheet to another one

    Quote Originally Posted by kvsrinivasamurthy View Post
    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,
    as expected your macro runs reall fast even in the worst condition.
    Thank you so much for your help!
    Last edited by ifritaeon61; 02-10-2021 at 01:37 AM.

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: How to copy merged rows from a Sheet to another one

    I saw there is no post from jindon.

  14. #14
    Registered User
    Join Date
    01-18-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    22
    Quote Originally Posted by kvsrinivasamurthy View Post
    I saw there is no post from jindon.
    You are right, I'm sorry for my lapsus!

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: How to copy merged rows from a Sheet to another one

    Ok. No problem. Good luck.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy selected merged rows to new excel sheet
    By maddyrafi1987 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 09-05-2019, 12:29 AM
  2. Copy and paste all rows of merged cells
    By Madz0220 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-23-2017, 09:56 AM
  3. Copy from merged cells in one sheet and paste to merged cells in another sheet
    By loroverde in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2015, 01:30 PM
  4. [SOLVED] Merge many sheets into one, re-order cols, include sheet name on merged sheet rows
    By EXLwiz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-11-2014, 02:48 AM
  5. Copy data from a sheet and Paste in another sheet into Merged cells
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-10-2011, 02:01 PM
  6. Replies: 4
    Last Post: 08-18-2010, 10:41 AM
  7. How to copy into multiple rows from merged cell
    By birisi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-18-2010, 09:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1