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?
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
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
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.
Source: Stackoverflow.com