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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,334
Messages
5,601,012
Members
414,421
Latest member
tonybear1994

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