How to add a worksheet after the active sheet

[nelsonchang]

Hi,
I have the following code to create a new Excel
worksheet. But the default is create it before the active
sheet. And I want to change the behaviour to make it the
position right after the active sheet. Please advise how.

Set oXLApp = New Excel.Application
Set oXLWBook = oXLApp.Workbooks.Add
Set oXLWSheet = oXLWBook.Worksheets("Sheet1")
'
oXLWBook.Sheet.Add
'
Set oXLWSheet = oXLWBook.ActiveSheet
oXLWSheet.Name = "S" + Str(SheetNo)

Thanks

 

[Ola Lövgren]

Hi

When you use the Add method in Excel you can use parameters defining where to place your added sheet
I suggest you write the code like this

Set oXLApp = New Excel.Applicatio
Set oXLWBook = oXLApp.Workbooks.Ad

Set oXLWSheet = oXLWBook.Worksheets.Add(After:=oXLWBook.Worksheets("Sheet1")
oXLWSheet.Name = "S" + Str(SheetNo

Good luck