Thursday, October 29, 2009

VBA: Copy x sheet from each file in folder


Sub CopySheet()
Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Dim filePath As String
filePath = "C:\Documents and Settings\user\Desktop\Baskets\"
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = filePath
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets("What Sells With My Item").Copy after:= _
basebook.Sheets(1)
ActiveSheet.Name = Mid(mybook.Name, 1, Len(mybook.Name) - 4)
mybook.Close

Next i
End If

End With
Application.ScreenUpdating = True
End Sub

No comments: