Append Worksheets of same structure from multiple Excel Files into one

Question

I want to combine several excel files into one. The excel files which i want to combine has the same sheets. So in that point, i want to combine the same sheets from different excel files.

I have this code to combine, hovewer this code merely combine the latest active sheet from the excel files:

Sub simpleXlsMerger()
    Dim bookList As Workbook
    Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
    Application.ScreenUpdating = False
    Set mergeObj = CreateObject("Scripting.FileSystemObject")
    Set dirObj = mergeObj.Getfolder("C:\Users\5545\Desktop\MI")
    Set filesObj = dirObj.Files
    For Each everyObj In filesObj
        Set bookList = Workbooks.Open(everyObj)
        Range("A2:IV" & Range("A28").End(xlUp).Row).Copy
        ThisWorkbook.Worksheets(1).Activate
        Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
        Application.CutCopyMode = False
        bookList.Close
    Next
End Sub

How can i specify the sheet("Day 1") which I wish in that code? The name of sheet which i want to combine is "Day 1"

Answer 1

Write:

With bookList.Worksheets("Day 1")
    .Range("A2:IV" & .Range("A28").End(xlUp).Row).Copy
End With

Instead of:

Range("A2:IV" & Range("A28").End(xlUp).Row).Copy

---

The two dots before Range in are important, they make sure that you refer to the correct worksheet twice:

Without them the code refers to the ActiveSheet.

Answer 2

You code looks like you copied code parts together, correct me if I am wrong. Therefore I applied the change you required and commented and renamed parts in the code for better understanding. If you dont find this usefull, please comment and I will delete the answer :)

Sub simpleXlsMerger()

    Application.ScreenUpdating = False
    'Define a Workbook Object for the Workbooks to pull data from
    Dim sourceWorkbook As Workbook
    'Define a Workbook Object of the Workbook to summarize data in
    Dim summaryWorkbook As Workbook
    'Set the summary Workbook to ThisWorkbook
    Set summaryWorkbook = ThisWorkbook

    Dim folderObject As Object
    Dim myFolder As Object
    Dim myFiles As Object
    Dim everyObj As Object
    Dim folderPath As String

    folderPath = "C:\Users\5545\Desktop\MI"

    Set folderObject = CreateObject("Scripting.FileSystemObject")
    Set myFolder = folderObject.Getfolder("C:\Users\5545\Desktop\MI")
    Set myFiles = myFolder.Files

    'For each file in your directory you set the sourceWorkbook equal to that file
    For Each File In myFiles
        Set sourceWorkbook = Workbooks.Open(File)
        'You define the range within this Workbook and copy the range
        sourceWorkbook.Range("A2:IV" & Range("A28").End(xlUp).Row).Copy
        'You append the summary sheet with the copied data
        summaryWorkbook.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
        'You close the file and open the next in the next loop
        sourceWorkbook.Close
    Next
    summaryWorkbook.Activate

End Sub

General remark for beginners to access Workbooks, Sheets and Ranges:

'Define an object container for workbooks,sheets and ranges you work with
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range

'A worksheet can be assigned by Name when its open, set to the Workbook the code is within, open a Workbook by Path or add a new one
'The workbook can then be accessed by using the variable e.g. wb
Set wb = ThisWorkbook
Set wb = Workbooks("WorkbookName")
Set wb = Workbooks.Open("FilePathIncludingNameAndFileType")
Set wb = Workbooks.Add

'Similarly worksheet objects can be assigned by Name, Index or added. Dont forget to use wb. to reference in which workbook to look for the sheet
Set ws = wb.Sheets("NameOfSheet")
Set ws = wb.Sheets(IndexOfSheet)
Set ws = wb.Sheets.Add

'Ranges are Objects within Worksheets and can be accessed by A1 notation e.g. "A1:C10" or by cell reference e.g. (Cells(1,1), Cells(10,3))
Set rng = ws.Range("A1NotationOfRange")
Set rng = ws.Range(Cells(RowIndex, ColumnIndex), Cells(RowIndex, ColumnIndex))