Combine first sheet of multiple workbooks into one workbook

Question

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?

There are a couple of characters that are valid in workbook names that are not valid in worksheet names ("[" and "]"), and worksheet names are limited to 31 characters while workbook names can be longer. So be prepared for errors or other unexpected behaviour if you try to set the worksheet name equal to the workbook name. — YowE3K
Okay thanks and if I only want to include sheet 1 and 4 of each workbook without changing the name? — Sner88
See the answer from user3598756, and just add a line saying .Worksheets(4).Copy After:=ThisWorkbook.Sheets(1) after .Worksheets(1).Copy After:=ThisWorkbook.Sheets(1), and also remove the ThisWorkbook.Sheets(2).name = .name line. — YowE3K
@Sner88, please stick to your current question and go to the end of it. I posted an answer and if it does what your original question asked then please accept it. If different issues arise then make new posts — user3598756

user3598756 user3598756 · Accepted Answer · 2016-09-06T08:38:59

try this:

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

Combine first sheet of multiple workbooks into one workbook

2 Answers