Results 1 to 7 of 7

How to combine two excel sheets!?

This is a discussion on How to combine two excel sheets!? within the Excel Questions forums, part of the Question Forums category; Hi, How can I combine two sheets with the same name ("sheet1") from diffrent excel files (names not related) into ...

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    6

    Question How to combine two excel sheets!?

    Hi,
    How can I combine two sheets with the same name ("sheet1") from diffrent excel files (names not related) into one sheet, also named "sheet1"?
    All of the files are located at the same folder.
    Please asist...

  2. #2
    Board Regular
    Join Date
    May 2008
    Location
    Sydney NSW Australia
    Posts
    263

    Default Re: How to combine two excel sheets!?

    Probably the best way would be to copy the required sheets from each folder into a new workbook.

    So right click on the sheet tab at the bottom, select "move or copy" sheets, then tick the box that says Create a copy, then pick "New book" from the drop down list etc. Then copy sheets from the other workbook into the new workbook.

    If you want to give the book the same name as other books you'll have to store it in a different location of course!

    Hope that helps
    Will

  3. #3
    GTO
    GTO is offline
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    5,079

    Default Re: How to combine two excel sheets!?

    Quote Originally Posted by TamarBal View Post
    Hi,
    How can I combine two sheets with the same name ("sheet1") from diffrent excel files (names not related) into one sheet, also named "sheet1"?
    All of the files are located at the same folder.
    Please asist...
    If you are looking to do this programmatically, more details would help. Is the same data in the same columns from the two workbooks(?), are there header rows(?) and so on.

  4. #4
    New Member
    Join Date
    Jan 2010
    Posts
    6

    Question Re: How to combine two excel sheets!?

    It is more complex then this:
    The number of rows at each sheet is changing from week to week and I need this combination every week. Simply copy and paste, even with a record macro, wouldn't help because the number of rows is changing. I have a title at the first row at each sheet, I don't care if it copy it twice.

    I need to merge the sheets into one at a dynamic process, due to the number of row that changing each week.

    Thanks

  5. #5
    Board Regular
    Join Date
    May 2008
    Location
    Sydney NSW Australia
    Posts
    263

    Default Re: How to combine two excel sheets!?

    How about linking the sheets with a copy and pastelink option? this would give you real-time updating whenever the linked sheet is open.

    Also if you ever do want write a sub that selects a block of information (to the last cell containing info) you can either record a Macro using the shift + Cntrl + down keys or you can insert the Current Region property into your Macro so:

    Range ("D4").CurrentRegion.Copy will select all the cells to the right and beneath D4 which contain information.

    Hope this is more useful

  6. #6
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,437

    Default Re: How to combine two excel sheets!?

    Hi
    Save a workbook with the following codes in the folder containing weekly data. run the macro
    Code:
     
    Sub Tamarbal()
    Dim z  As Long, e As Long, d As Long
    Dim f As String
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    d = 2
    Sheets("Sheet1").Select
    Cells(1, 1) = "=cell(""filename"")"
    Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
    Cells(2, 1).Select
    f = Dir(Cells(1, 2) & "*.xls")
        Do While Len(f) > 0
        ActiveCell.Formula = f
        ActiveCell.Offset(1, 0).Select
        f = Dir()
        Loop
    z = Cells(Rows.Count, 1).End(xlUp).Row
        For e = 2 To z
            d = Sheets("sheet1").Range("C65536").End(xlUp).Row + 2
            Cells(d, 2) = Cells(e, 1)
            If Cells(e, 1) <> ActiveWorkbook.Name Then
            Workbooks.Open Filename:=Cells(1, 2) & Cells(e, 1)
            Worksheets("Sheet1").UsedRange.Copy
            ActiveWorkbook.Close False
            Cells(d, 3).PasteSpecial
            End If
        Next e
        Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "collating is complete."
    End Sub
    It lists all file names in co A & b and contents of sheet1 col C onwards
    Ravi

  7. #7
    New Member
    Join Date
    Jan 2010
    Posts
    6

    Default Re: How to combine two excel sheets!?

    Ill try it now.
    Thanks a lot

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com