+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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,321
    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,159
    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,159
    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
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,269

    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
    291
    how did you combine the first 3 sheets in the merged list tab?

  10. #10
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,269

    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
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,143

    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
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,143

    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.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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