Menu
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?
909
To quickly insert a new worksheet at the end of the existing worksheets, click the Insert Worksheet tab at the bottom of the screen. To insert a new worksheet in front of an existing worksheet, select that worksheet and then, on the Home tab, in the Cells group, click Insert, and then click Insert Sheet.
Can't insert a new worksheet or delete an existing sheet? The option to add new sheet is greyed out? If the workbook structure is protected with a password, you're unable to add, delete, move, copy, rename, hide or unhide any sheets.
Shift to the worksheet you want to copy to end of workbook, right click the sheet tab, and then click Move or Copy from the context menu. See screenshot: 2. In the Move or Copy dialog box, select (move to end) in the Before sheet box, and check the Create a copy box, then click the OK button.
Try this
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.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
