+ Reply to Thread
Results 1 to 24 of 24

How to automatically combine several worksheets into one worksheet...

  1. #1
    Registered User
    Join Date
    06-28-2008
    Location
    UK
    Posts
    9

    How to automatically combine several worksheets into one worksheet...

    Hi peeps

    I want to combine data from several worksheets into one worksheet.

    For example, I have data in Sheet1 (Columns A,B,C), data in Sheet2 (Columns A,B,C), data in Sheet3 (Columns A,B,C) all with varying amounts of rows. (All the rows contain text data).

    I need to combine all of the data from the 3 sheets into a single sheet, Sheet4 (Columns A,B,C), eliminating the empty rows.

    I've been looking into this for a while, and can't find anything that really helps. Anyone got any pointers of what to look into?

    Any help will be beautiful.

    Cheers

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    that is hardly an arduous task!
    simply copy paste all onto one sheet then sort .

  3. #3
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Try this, it moves data from columns A-C from sheets 1-3 to sheet4, if a blank appears in a row the entire row is deleted:
    Please Login or Register  to view this content.
    Not all forums are the same - seek and you shall find

  4. #4
    Registered User
    Join Date
    06-28-2008
    Location
    UK
    Posts
    9
    Er - sorry, I want to do it automatically!

    I'm not a total doughnut, only partly.

  5. #5
    Registered User
    Join Date
    06-28-2008
    Location
    UK
    Posts
    9
    Quote Originally Posted by Simon Lloyd
    Try this, it moves data from columns A-C from sheets 1-3 to sheet4, if a blank appears in a row the entire row is deleted:
    Please Login or Register  to view this content.

    You are star, thanks mate - much appreciated!

  6. #6
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    wherever you see Sheet4 Change Sheet4 to be your destination sheet, this code will work if you only have 2 sheets or 200 sheets but only for columns A-C unless you change the C wherever you see it.

  7. #7
    Registered User
    Join Date
    06-28-2008
    Location
    UK
    Posts
    9
    Hey Simon

    The code works fine, apart the delete empty rows bit.

    If the cell contains a formula (which all of my cells do) the 'SpecialCells(xlCellTypeBlanks)' evaluates them as not being blank, and states that there are no cells to delete.

    Is there a way round this?

    Maybe to copy the value and not the formula, or to check to see if the value of the cells is blank?

    Thanks mate

  8. #8
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,676

    Merge multiple worksheets in to one.

    This is another way to do it, with excel functions only. NO VB coding.
    Its kind of basic, but works for upto five or six sheets.
    See attached example.
    It combines first three sheets in to the "Merged List".
    Hope it helps osmeone or at least gives some ideas to work with.
    modytrane
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    02-10-2007
    MS-Off Ver
    Excel 2010
    Posts
    293
    how did you combine the first 3 sheets in the merged list tab?

  10. #10
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,676

    Merge Three sheets into one

    On the sheet marked "Merged List" follow the formulas.
    You may have to unhide all columns [I believe the first two columns were hidden]. Anyway, I first count number of rows in each of the three sheets. I just picked one column [in my case it was the column that had project name]. I am counting number of rows in that column that have text length of more than one character. This gives me the number of rows to copy for each sheet. Once I know this I wrote formulas to copy cells in corresponding columns from first sheet and use that count number, once you reach the count for first sheet, the IF statement switches to second part and moves to second sheet and so on.
    That's why I said this method would work for up to five or six sheets. After that you might run into problems with IF statements.
    Good Luck,
    modytrane

  11. #11
    Registered User
    Join Date
    07-25-2013
    Location
    Horsham, England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to automatically combine several worksheets into one worksheet...

    That code works great bu how can I specifiy which sheets as in some cases I don't want to combine all of them but just two specific ones. PLEASE

  12. #12
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: How to automatically combine several worksheets into one worksheet...

    Try this
    Please Login or Register  to view this content.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  13. #13
    Registered User
    Join Date
    07-25-2013
    Location
    Horsham, England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to automatically combine several worksheets into one worksheet...

    Hi Arli1201,

    That macro does not seem to quite work, having done i and expected a combination of two worksheets and some 30000 rows however I got 700 and only from one sheet :-/ How do I remove the " remove blank rows"?

  14. #14
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: How to automatically combine several worksheets into one worksheet...

    I think its best we discuss this in a separate thread. We can check on your code in detail and find out whats causing the errors.

  15. #15
    Registered User
    Join Date
    10-28-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: How to automatically combine several worksheets into one worksheet...

    Hi Guys,

    Need similar help. I'm trying to combine multiple sheets to a master sheet. But I want the data to be filtered as per the date. We are using an excel till we get a system in place for this.

    Column A till L has data which is updated on a daily basis. Out of this, Column A has the date entered. We have 7 sheets like this will have the same data type and want to combine this into one sheet which should be filtered by the Date. Can anyone please help me with this?

    Do let me know if you need any clarification on this.

  16. #16
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: How to automatically combine several worksheets into one worksheet...

    Nitesh,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

  17. #17
    Forum Contributor
    Join Date
    09-07-2013
    Location
    chennai
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: How to automatically combine several worksheets into one worksheet...

    use ASAP utilities tool in excel....

  18. #18
    Registered User
    Join Date
    11-02-2013
    Location
    Somewhere, else
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How to automatically combine several worksheets into one worksheet...

    Quote Originally Posted by arlu1201 View Post
    Try this
    Please Login or Register  to view this content.

    Thanks sir!This is great! it works on mine..i just ignore the delete rows error

  19. #19
    Registered User
    Join Date
    10-29-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: How to automatically combine several worksheets into one worksheet...

    Thanks, Great Code.

    Would you please share the code with an additional column which will fill the sheet name, when we execute this code.

    Quote Originally Posted by arlu1201 View Post
    Try this
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    03-11-2014
    Location
    Niagara Frontier
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: How to automatically combine several worksheets into one worksheet...

    Tryring to combine 12 worksheets into a single master.
    My data is in columns O to AA.
    However the data is based upon formulas that consolidate the values in A:L.
    When I run the above code it returns reference errors.
    Is there a way to pull just the values of the range onto the single master and not the formulas?
    Thanks for your assistance on this matter.

  21. #21
    Registered User
    Join Date
    03-10-2014
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: How to automatically combine several worksheets into one worksheet...

    I found this works to combine multiple sheets. I created a new sheet ("combinedsheet") for the four I wanted to merge together
    David


    Please Login or Register  to view this content.
    Last edited by arlu1201; 04-14-2014 at 07:46 AM.

  22. #22
    Registered User
    Join Date
    04-17-2010
    Location
    London
    MS-Off Ver
    Mac Excel 2011
    Posts
    6

    Re: How to automatically combine several worksheets into one worksheet...

    This is so nearly exactly what i wanted.
    When i used my modified version of this code it seemed to work but if the values on sheets 1, 2 and 3 had formulas in they would not copy the resulting answer to the combined sheet (sheet 4).

    Is there a way of modifying this code to paste values only?

    Regards
    Kevin

  23. #23
    Registered User
    Join Date
    02-15-2015
    Location
    NEW YORK, NEW YORK
    MS-Off Ver
    2010
    Posts
    1

    Re: How to automatically combine several worksheets into one worksheet...

    PLEASE HELP FIX THIS:

    Sub move_data()
    Dim Sht As Worksheet
    Dim Rng As Range
    For Each Sht In Sheets
    If Sht.Name = "CONSOLIDATED" Then
    ElseIf Sht.Name = "AP - OPEN" Or Sht.Name = "AR - OPEN" Or Sht.Name = "ZGRIR" Then
    Set Rng = Sht.Range("A4:F" & Sht.Range("F" & Rows.Count).End(xlUp).Row)
    Rng.Copy Destination:=Sheets("CONSOLIDATED").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
    Next Sht
    With Sheets("consolidated").Range("A4:F" & Range("F" & Rows.Count).End(xlUp).Row). _
    PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    End Sub

  24. #24
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: How to automatically combine several worksheets into one worksheet...

    Hi Scott, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    (Also, please take a look at the rules regarding the use of code tags when posting VBA)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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