将多个工作簿的第一张纸合并为一个工作簿 [英] Combine first sheet of multiple workbooks into one workbook

问题描述

我有一个宏，可以将不同的工作簿合并为一个主Excel工作簿:

I have a macro to combine different workbooks into one master Excel workbook:

Sub GetSheets()
Path = "\Users\myname\Documenten\Test\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
        For Each Sheet In ActiveWorkbook.Sheets
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
Loop
End Sub

我如何仅将每个工作簿的第一个工作表包括到主工作簿中?

How would I include only the first worksheet of each workbook into the master workbook?

我如何将工作表重命名为它来自的工作簿的名称?

How would I rename the worksheets to the name of the workbook it comes from?

推荐答案

尝试一下:

Option Explicit

Sub GetSheets()
    Dim Path As String, fileName As String
    Dim Sht As Worksheet

    Path = "\Users\myname\Documenten\Test\"
    fileName = Dir(Path & "*.xls")
    Do While fileName <> ""
        Workbooks.Open fileName:=Path & fileName, ReadOnly:=True
        With ActiveWorkbook
            .Worksheets(1).Copy After:=ThisWorkbook.Sheets(1)
            ThisWorkbook.Sheets(2).name = .name
        End With
        ActiveWorkbook.Close
        fileName = Dir()
    Loop
End Sub

这篇关于将多个工作簿的第一张纸合并为一个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！