Adding sheets to end of workbook in Excel (normal

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

A common mistake is

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

which leads to Error 1004. Although it is not clear at all from the official documentation, it turns out that the 'After' parameter cannot be an integer, it must be a reference to a sheet in the same workbook.

Answer 2

Try this

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

Answer 3

Be sure to fully qualify your sheets with which workbook they are referencing!

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

Answer 4

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

should probably be

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