Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding New Sheets
Hi Dave,
Thanks for your reply and your codes Yes, I should use "After:=" and it works perfectly Regards Len |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding sheets | Excel Discussion (Misc queries) | |||
Adding numbers from other sheets? | Excel Programming | |||
Adding Sheets | Excel Discussion (Misc queries) | |||
Macro for adding more sheets | Excel Discussion (Misc queries) | |||
adding sheets together | Excel Programming |