Hi,

After few attempts, the following codes fails to add new sheets and
rename its sheet to this workbook after copying worksheet from each
excel file under MPV folder

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
Before:=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

Any help will be appreciated and thanks in advance


Regards
Len

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 _
Before:=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 Before:= 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
> Before:=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

Hi Dave,


Thanks for your reply and your codes
Yes, I should use "After:=" and it works perfectly


Regards
Len