Inserting Worksheets

[KDP36]

Hi all,

I'm trying to combine sheet1's of different workbooks, and all insert into one large workbook. Please see my code below:

Sub GetSheets()
Path = "C:\Training\VBA\Practice\"
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

However, apparently what has happened was the macro inserted ALL worksheets from all workbooks and combined into one, which is not what I'm looking for. How do I only insert sheet1's and leave out the rest?
Evidently my experience with VBA is limited, so any input and help on this issue here is much appreciated. Also, if I were to insert the first sheet1 from different workbooks if their respective names were different (say Maximum, Minimum, Deductible, etc.), how would I do that? I guess that's the second question I have.
Thanks in advance again!

KDP

 

[djreiswig]

Sub GetSheets()
    Path = "C:\Training\VBA\Practice\"
    Filename = Dir(Path & "*.xls")
    Do While Filename <> ""
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
        ActiveWorkbook.Sheets(1).Copy After:=ThisWorkbook.Sheets(1)
        Workbooks(Filename).Close
        Filename = Dir()
    Loop
End Sub

You don't need the For...Next loop if you just want to copy the first sheet. That's why it was copying all of the sheets.

Your second question...Sheet1 is always the first sheet in the workbook. If you look in the Project area in the VBA window you can see which sheet is sheet1. It doesn't matter what it is named. It is still the first sheet.

 

[KDP36]

Thank you for the prompt response djreiswig. That was very helpful! Much appreciated.

 

[djreiswig]

Glad to help.