Inserting Worksheets

KDP36

New Member
Joined
Jan 10, 2014
Messages
16
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

djreiswig

Well-known Member
Joined
Mar 13, 2010
Messages
523
Code:
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

New Member
Joined
Jan 10, 2014
Messages
16
Thank you for the prompt response djreiswig. That was very helpful! Much appreciated.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,816
Messages
5,855,795
Members
431,764
Latest member
scottishbigyin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top