Recently, I created a macro to put new worksheets to the right of the active worksheet. Today, I installed the Solver add-in and my code failed. I don’t know what Solver is doing behind the scenes, but apparently it’s adding a worksheet to a workbook somewhere. I changed the code to only work when the workbook is visible.
As long as I was changing the code, I also changed it to accommodate Tushar’s comment about Excel 2007 putting the sheet at the end.
As long as I was changing the code, I wanted to accommodate Ken’s comment about inserting multiple sheets. I didn’t have much luck on this one. There are a couple ways to insert multiple sheets; see Microsoft’s Inserting a new worksheet.
When I selected two sheets and clicked Insert > Worksheet, Excel put the two new worksheets in between the selected worksheets. Actually, it puts them to the left of the right-most worksheet, which I saw when I selected/inserted three worksheets. Then, the NewSheet event is called once for every sheet that’s inserted. Once the new sheets are inserted and before the events are fired, there appears to be no way to know how many sheets have been inserted or which sheets were originally selected.
Inserting multiple sheets by inserting a template is a little different. The NewSheet event is only fired once regardless of the number of sheets in the template. What isn’t different, unfortunately, is the lack of information about the number of sheets.
I could keep track of the sheet count per workbook and compare the before and after, but I won’t be doing that. If you have any other ideas, let me know. Here’s the updated code:
Dim bVisible As Boolean
Dim Wn As Window
bVisible = False
For Each Wn In Wb.Windows
If Wn.Visible Then
bVisible = True
Exit For
End If
Next Wn
If bVisible Then
If Sh.Index < Wb.Sheets.Count Then
Sh.Move , Wb.Sheets(Sh.Index + 1)
End If
End If
End Sub
Posting code? Use <pre> tags for VBA and <code> tags for inline.