Ron,
Thanks for the help. I am still struggling. I am not a Visual Basic geek yet
but am learning. I took your script below to do this. I copied into my C
drive a file called Creating America which has all 60 work sheets. I am
confused on some of the language listed below. Sorry to sound stupid but I do
not understand the Dim basebook as Workbook. Maybe this is over my head but
hoping you can help. This is a mail list. from each representive and I want
to merge them so I can do one run on my mailing equipment. The only change I
did to your VB script was to change the directory for the files. I obviously
need to do more but am somewhat confused. Here is the error I am getting...
Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs
Line: 2
Char: 14
Error: Expected end of statement
Code: 800A0401
Source: Microsoft VBScript compilation error
Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Creating America"
.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(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name
mybook.Close
Next i
End If
End With
Application.ScreenUpdating = True
End Sub
