Run time error - Adding Workshet
 

I am using following procedure for adding worksheets in workbook, first time following function it works perfect and all the tab created alphabatically with the different name(abc, mno, stv, xys), but second time it give me following error

Method 'Move' of Object '_worksheet' failed;

If I comment following line, it wont give me error, it run perfectly except workbook tab name add in reverse chronolofical order like (xys, stv, mno, abc)

ExcelSht.Move befo=Worksheets(Worksheets.Count)

Lte me know whats wong I am doing? Or is there any way that I can sort worksheet alphabatically.

*--------------------------------------------------------
Dim ExcelApp As New Excel.Application
Dim ExcelSht As New Excel.Worksheet
Dim ExcelWkb As New Excel.Workbook
Dim rsManagersDis As New ADODB.Recordset

Set ExcelApp = CreateObject("Excel.Application")
MyExcel = True
ExcelApp.Visible = True

Set ExcelWkb = ExcelApp.Workbooks.Add

rsManagersDist.ActiveConnection = CurrentProject.Connection
rsManagersDist.CursorLocation = adUseClient
rsManagersDist.Open "Select distinct [Manager Name] rom tmpPMExcel order by [Manager Name] ) "

Do Until rsManagersDist.EOF
j2 = rsManagersDist("expr1")
Set ExcelSht = ExcelWkb.Worksheets.Add
ExcelWkb.Worksheets("Sheet1").Visible = xlSheetVeryHidden
ExcelSht.Visible = xlSheetVisible
ExcelSht.Move befo=Worksheets(Worksheets.Count)
ExcelSht.name = j2
ExcelSht.PageSetup.PrintGridlines = True
ExcelSht.PageSetup.Orientation = xlPortrait
rsManagersDist.MoveNext
Loop
*--------------------------------------------------------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.601 / Virus Database: 382 - Release Date: 02/29/2004

Run time error - Adding Workshet
 

Hi
for sorting worksheets you may have a look at
http://www.cpearson.com/excel/sortws.htm

--
Regards
Frank Kabel
Frankfurt, Germany

Ashish Kanoongo wrote:
I am using following procedure for adding worksheets in workbook,
first time following function it works perfect and all the tab
created alphabatically with the different name(abc, mno, stv, xys),
but second time it give me following error

Method 'Move' of Object '_worksheet' failed;

If I comment following line, it wont give me error, it run perfectly
except workbook tab name add in reverse chronolofical order like
(xys, stv, mno, abc)

ExcelSht.Move befo=Worksheets(Worksheets.Count)

Lte me know whats wong I am doing? Or is there any way that I can
sort worksheet alphabatically.

*--------------------------------------------------------
Dim ExcelApp As New Excel.Application
Dim ExcelSht As New Excel.Worksheet
Dim ExcelWkb As New Excel.Workbook
Dim rsManagersDis As New ADODB.Recordset

Set ExcelApp = CreateObject("Excel.Application")
MyExcel = True
ExcelApp.Visible = True

Set ExcelWkb = ExcelApp.Workbooks.Add

rsManagersDist.ActiveConnection = CurrentProject.Connection
rsManagersDist.CursorLocation = adUseClient
rsManagersDist.Open "Select distinct [Manager Name] rom tmpPMExcel
order by [Manager Name] ) "

Do Until rsManagersDist.EOF
j2 = rsManagersDist("expr1")
Set ExcelSht = ExcelWkb.Worksheets.Add
ExcelWkb.Worksheets("Sheet1").Visible = xlSheetVeryHidden
ExcelSht.Visible = xlSheetVisible
ExcelSht.Move befo=Worksheets(Worksheets.Count)
ExcelSht.name = j2
ExcelSht.PageSetup.PrintGridlines = True
ExcelSht.PageSetup.Orientation = xlPortrait
rsManagersDist.MoveNext
Loop
*--------------------------------------------------------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.601 / Virus Database: 382 - Release Date: 02/29/2004