How to combine two excel sheets!?

TamarBal

New Member
Joined
Jan 18, 2010
Messages
6
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...
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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.
 
Upvote 0
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 :confused:
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top