将来自多个Excel文件的相同结构的工作表追加到一个 [英] Append Worksheets of same structure from multiple Excel Files into one

问题描述

我想将几个excel文件合并为一个.我要合并的Excel文件具有相同的工作表.因此，在这一点上，我想合并来自不同excel文件的相同工作表.

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.

我可以结合使用以下代码，但是此代码仅结合了excel文件中最新的活动工作表:

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

如何在代码中指定所需的sheet("Day 1")?我要合并的工作表名称为第1天"

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"

推荐答案

写:

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

代替:

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

Range中的两个点很重要，它们确保您两次引用正确的工作表:

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

没有它们，代码指的是ActiveSheet.

Without them the code refers to the ActiveSheet.

这篇关于将来自多个Excel文件的相同结构的工作表追加到一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！