Adding New Sheets
 

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
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

Any help will be appreciated and thanks in advance


Regards
Len

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

Adding New Sheets
 

Hi Dave,


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


Regards
Len