Extended sheet selection in Excel 2013 - VBA

Question

Could you please help to understand why this code works in older versions of Excel but not in Excel 2013? The line with replace:=false is ignored. Is it related to some central settings? What should be modified?

Sub test()
    Dim i
    Dim n

    n = InputBox("type n")

    For i = 1 To n
        If i = 1 Then ThisWorkbook.Sheets(i).Select
        If i > 1 Then ThisWorkbook.Sheets(i).Select Replace:=False
    Next i
End Sub

Apparently it's a [recently introduced bug](https://social.technet.microsoft.com/Forums/office/en-US/a6ca17bd-c72f-4276-aa4e-75b2bcf120e0/excel-2013-update-15048411001-has-broken-the-vba-worksheetssheet2select-replacefalse?forum=excel). Works as expected in 2016 and earlier versions. — GSerg, Aug 29 '16 at 07:27
Possible duplicate of [ThisWorkbook.Sheets(1).Select (False) Not Working](http://stackoverflow.com/questions/38724223/thisworkbook-sheets1-select-false-not-working) — Ralph, Aug 29 '16 at 07:35

score 0 · Answer 1 · answered Sep 22 '16 at 08:56

I tried your code in Excel 2013. Possible reasons could be that ThisWorkbook is not the active workbook or that sheets are invisible. You should only use the select function on the active workbook. See below your code updated - I used it in a workbook with hidden sheets and it worked. Look at the immediate window in the VBA Editor (Ctrl+G) to see the Debug messages. Note that hidden sheets count as well for your variable 'n'. Hope it helps.

Sub test()
    Dim i
    Dim n
    Dim foundFirstVisSheet As Boolean
    foundFirstVisSheet = False

    n = InputBox("type n")
    Dim mySh As Worksheet


    For i = 1 To n

        Set mySh = ActiveWorkbook.Sheets(i)
        If mySh.Visible = xlSheetVisible Then
            If Not foundFirstVisSheet Then
                foundFirstVisSheet = True
                mySh.Activate
            End If
            Debug.Print mySh.Name & " is visible"
            mySh.Select Replace:=False
        Else
            Debug.Print mySh.Name & " is invisible and cannot be selected"
        End If

    Next i
End Sub

Extended sheet selection in Excel 2013 - VBA

1 Answers1