New Worksheet Location II

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:

Private Sub myApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
       
    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
Posted in Uncategorized


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.