vba - Adding sheets to end of workbook in Excel (normal method not working?)

Question

This is the VBA code im using to try add a new sheet to the last place in the workbook

mainWB.Sheets.Add(After:=Sheets(Sheets.Count)).Name = new_sheet_name

I saw this in a similar question on this site. Its not working.

I do this in a loop and each sheet gets added to the second position in the sheets. There are 2 sheets that are permanently there (info and summary) and I then precede to add 5 more called "test" 1 through 5. I always end up with the sheets in this order:

Info, sheet5, sheet4, sheet3, sheet2, sheet1, Summary

But what I want/was expecting was:

Info, Summary, sheet1, sheet2, sheet3, sheet4, sheet5

(the loop does produce them in the expected order so the problem isn't there.)

If I swap the summary and info sheets before I start then they are in the opposite places when I'm done.

What am I doing wrong?

Answer 1

Try this

mainWB.Sheets.Add(After:=mainWB.Sheets(mainWB.Sheets.Count)).Name = new_sheet_name