Adding New Sheets
You could try:
Dim i%, SheetArg$()
Dim sPath As String
Dim sFile As Variant
Dim oSht As Integer
Dim ShtFile As String
Windows("ABC.xls").Activate
oSht = Worksheets.Count
sPath = "D:\My Documents\MPV\"
sFile = Dir(sPath & "*.xls", vbNormal)
Do While sFile < ""
Workbooks.Open Filename:=sPath & sFile
Workbooks(sFile).Sheets(1).Copy _
Befo=Workbooks("ABC.xls").Sheets(oSht)
ShtFile = Left(sFile, InStrRev(sFile, ".") - 3)
ActiveSheet.Name = ShtFile
oSht = oSht + 1
Workbooks(sFile).Close SaveChanges:=False
sFile = Dir()
Loop
Did you really mean to use Befo= and not After:=????
On 07/15/2010 01:57, Len wrote:
Dim i%, SheetArg$()
Dim sPath As String
Dim sFile As Variant
Dim oSht As Integer
Dim ShtFile As String
Windows("ABC.xls").Activate
sPath = "D:\My Documents\MPV\"
sFile = Dir(sPath& "*.xls", vbNormal)
Workbooks.Open Filename:=sPath& sFile
Do While sFile< ""
oSht = Worksheets.Count
Workbooks(sFile).Sheets(1).Copy
Befo=Workbooks("ABC.xls").Sheets(oSht)
ShtFile = Left(sFile, InStrRev(sFile, ".") - 3)
ActiveSheet.Name = ShtFile
oSht = oSht + 1
Workbooks(sFile).Close SaveChanges:=False
sFile = Dir()
Loop
--
Dave Peterson
|