Copy a sheet from all workbooks in a folder

[ron]

Hello

I require help creating a macro.
From a workbook I want to execute a macro to select a folder, and copy into the active workbook a specifically named worksheet, (Index), from all of the workbooks in the folder.

Thanks

Ron

[matthewspatrick]

Assumes all of those "Index" workbooks are closed at runtime...


Sub CopyIndex

Dim fso As Object, fld As Object, fil As Object
Dim wb As Workbook
Dim ws As Worksheet

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder("c:\folder\subfolder")
For Each fil In fld.Files
If UCase(Right(fil.Name, 4)) = ".XLS" Then
Set wb = Workbooks.Open(fil.Path)
Set ws = wb.Worksheets("Index")
With ActiveWorkbook
ws.Copy After:=.Worksheets(.Worksheets.Count)
End With
wb.Close False
End If
Next

Set fil = Nothing
Set fld = Nothing
Set fso = Nothing

MsgBox "Done"

End Sub

[ron]

Thanks for the quick reply. When testing the code it copies all of the sheets into the active workbook not just the ?Index? named sheet and when each workbook in the folder is processed the previously copied sheets are deleted so when the macro completes there are only the original sheets in the active workbook.

What am I doing wrong?

Ron

[Norie]

Try changing ActiveWorkbook to ThisWorkbook.

[ron]

Thanks Norie that did the trick!

Now I want some more functionality such as the user can select the folder location rather then hard coding the path and the originating workbook can be in the same folder.

Similar to the code malik641 submitted at: http://www.vbaexpress.com/kb/getarticle.php?kb_id=829 (http://www.vbaexpress.com/kb/getarticle.php?kb_id=829) ,but his copies all the worksheets from all the workbooks in one folder into the active workbook, how do I adjust the code to copy only a sheet named “Index”.