Hi Moogie,
See if you can use something along the following lines:
<pre>
Sub SheetstoNewBook()
'copy 31 excel files to one new workbook (this one!)
'exclude last 2 sheets from each workbook
Dim TheDir As String, vaFileName As Variant
TheDir = ThisWorkbook.Path
'don't know your intended directory,
'therefore save thisworkbook in target directory
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = TheDir
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For Each vaFileName In .FoundFiles
If vaFileName <> TheDir & "" & ThisWorkbook.Name Then
'only try to open workbooks other than this one
Workbooks.Open (vaFileName)
CopySheets
'go to copy sheets routine
Application.DisplayAlerts = False
'avoid warning message when copied workbook is closed
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
Next
End If
End With
Application.ScreenUpdating = True
End Sub
Sub CopySheets()
Dim newsheet As Worksheet, j As Integer, Last As Integer
Last = 0
For j = ActiveWorkbook.Sheets.Count To 1 Step -1
Last = Last + 1
If Last > 2 Then
ActiveWorkbook.Worksheets(j).UsedRange.Copy
Set newsheet = ThisWorkbook.Sheets.Add
'add sheet to copy to
newsheet.Range("A1").PasteSpecial
Application.CutCopyMode = False 'clear the clipboard
End If
Next j
End Sub
</pre>
HTH