+ Reply to Thread
Results 1 to 12 of 12

How to merge all worksheets into one

  1. #1
    Registered User
    Join Date
    05-19-2021
    Location
    England
    MS-Off Ver
    Pro Plus 2016
    Posts
    9

    How to merge all worksheets into one

    Hi,

    I used to copy all worksheets and paste into one single worksheet which is tedious and prone to human error.

    How can I merge the different worksheets (with the same columns) in a workbook into one single worksheet without using VBA/macro or power query?

    Similarly, how can i merge the different workbooks into one without using VBA/macros or power query?

    Thanks.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: How to merge all worksheets into one

    Delete by author. OP not interested

    Reinstated by Mod (do NOT delete content that may be of interest to other forum members).

    See https://www.rondebruin.nl/win/section3.htm
    or
    https://www.rondebruin.nl/win/addins/rdbmerge.htm
    Last edited by AliGW; 08-04-2021 at 02:26 AM.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to merge all worksheets into one

    Are you really using Excel 2010, or something newer?

    Please go back to your earlier thread and thank your helper, then mark the thread as solved: https://www.excelforum.com/excel-for...mn-header.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    05-19-2021
    Location
    England
    MS-Off Ver
    Pro Plus 2016
    Posts
    9

    Re: How to merge all worksheets into one

    See Post #11
    Last edited by Starduzz; 08-04-2021 at 02:48 AM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to merge all worksheets into one

    1. Please update your forum profile - if it's Excel 2016 for Mac, add that, too.
    2. If it's Excel 2016 for PC, then you DO have PowerQuery - it is called Get & Transform and is on the Data ribbon.
    3.

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. As you are new, I'll do it for you today.)

  6. #6
    Registered User
    Join Date
    05-19-2021
    Location
    England
    MS-Off Ver
    Pro Plus 2016
    Posts
    9

    Re: How to merge all worksheets into one

    Quote Originally Posted by Pepe Le Mokko View Post
    Delete by author. OP not interested
    Thank you for the advice!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to merge all worksheets into one

    Do you still need help with this? As I said before, your version of Excel has PowerQuery (Get & Transform Data).

    EDIT: As you seem to want a VBA solution, I've moved this to the appropriate forum section.
    Last edited by AliGW; 08-04-2021 at 02:42 AM.

  8. #8
    Registered User
    Join Date
    05-19-2021
    Location
    England
    MS-Off Ver
    Pro Plus 2016
    Posts
    9

    Re: How to merge all worksheets into one

    Strangely, my version does not have the Get Data Sources in the Get & Transform group.

    Attachment 742806
    Last edited by AliGW; 08-04-2021 at 02:44 AM. Reason: PLEASE don't quote unnecessarily!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to merge all worksheets into one


  10. #10
    Registered User
    Join Date
    05-19-2021
    Location
    England
    MS-Off Ver
    Pro Plus 2016
    Posts
    9

    Re: How to merge all worksheets into one

    Windows version
    Last edited by AliGW; 08-04-2021 at 02:46 AM. Reason: PLEASE don't quote unnecessarily!

  11. #11
    Registered User
    Join Date
    05-19-2021
    Location
    England
    MS-Off Ver
    Pro Plus 2016
    Posts
    9

    Re: How to merge all worksheets into one

    I'm trying to consolidate multiple worksheets into one specific worksheet and found the following VBA code:

    Please Login or Register  to view this content.
    However, i like to copy only rows from A9 onwards (instead of A1) in each worksheet and transfer the consolidated rows to a specific worksheet (starting from cell b6) in a specific workbook.

    Is there any way to amend the vba code to follow the above scenario?

    Appreciate your help and advice plse.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to merge all worksheets into one

    OK - see the link in my previous post.

    Someone else will be able to help you with the VBA - please be patient.

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

+ 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. [SOLVED] VBA merge worksheets in a folder - each file in a column, multiple worksheets below
    By bigmantitus in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 02-11-2021, 12:59 PM
  2. Replies: 0
    Last Post: 04-18-2019, 09:13 AM
  3. Replies: 1
    Last Post: 09-10-2012, 06:11 AM
  4. Merge 2 Worksheets (Same concept as RegEdit Merge or Outlook Vcard)
    By stevedes7 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-20-2009, 11:17 PM
  5. merge two worksheets
    By bluntz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-19-2009, 03:18 AM
  6. Merge Worksheets
    By newbieexceldude in forum Excel General
    Replies: 11
    Last Post: 05-12-2009, 10:36 AM
  7. [SOLVED] Merge Worksheets
    By Mark Jackson in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-08-2005, 06:05 PM

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