none
VBA to activate specific sheets in a workbook RRS feed

  • Question

  • I need to be able to activate a specific sheet in another workbook based on a the value of a cell in the active workbook.

    Lets say in workbook 1 on the sheet "Checklist" in cell E5 is a value usually a number but can also have text. 

    In workbook 2 there is a corresponding sheet named exactly as the data in cell E5 of the other workbook.

    I need to be able to activate the sheet in workbook 2 using the value in cell E5 of workbook 1. 

    I have the following code (which works nicely) to switch between the two open workbooks. My question would be is there a way to store the value in cell E5, switch workbooks then activate the respective sheet?

        For i = 2 To Workbooks.Count
        If Workbooks(i).Name <> ActiveWorkbook.Name Then
        Workbooks(i).Activate
        Exit For
        End If
        Next i

    Monday, February 1, 2016 2:49 PM

Answers

  • Let's try forcing the value to be a string: the code is looking for the 121st worksheet, which does not exist. I had interpreted your post about number as being along the lines of 1, 2, 3 meaning the first, second, third sheets....

    Dim vName As String 'Changed here

    vName = ActiveWorkbook.Worksheets("Checklist").Range("E5").Value

    For i = 2 To Workbooks.Count If Workbooks(i).Name <> ActiveWorkbook.Name Then Workbooks(i).Activate

    Workbooks(i).Worksheets(vName).Activate Exit For End If Next i



    Monday, February 1, 2016 4:18 PM

All replies

  • Try something like this:

    Dim vName As Variant

    vName = ActiveWorkbook.Worksheets("Checklist").Range("E5").Value

    For i = 2 To Workbooks.Count If Workbooks(i).Name <> ActiveWorkbook.Name Then Workbooks(i).Activate

    Workbooks(i).Worksheets(vName).Activate Exit For End If Next i


    Monday, February 1, 2016 3:23 PM
  • I had something similar to that before; however, I keep getting: Run-time error 9 Subscript out of range. On the following line:

    Workbooks(i).Worksheets(vName).Activate

    Monday, February 1, 2016 3:47 PM
  • That means that the workbook that your code is choosing doesn't actually have that sheet - you may not be activating the workbook you think you are

    Sub TestMacro()
        Dim vName As Variant
        Dim S As Worksheet
        Dim strM As String
        Dim i As Integer
        
        vName = ActiveWorkbook.Worksheets("Checklist").Range("E5").Value
        
        strM = "Looking for """ & vName & """" & Chr(10) & "But I have found these in "
        
        For i = 2 To Workbooks.Count
            If Workbooks(i).Name <> ActiveWorkbook.Name Then
                strM = strM & Workbooks(i).Name & Chr(10)
                Workbooks(i).Activate
                For Each S In Workbooks(i).Worksheets
                    strM = strM & """" & S.Name & """" & Chr(10)
                Next S
                Exit For
            End If
        Next i
        MsgBox strM
    End Sub




    Monday, February 1, 2016 3:58 PM
  • Getting closer. I get this message box:

    Monday, February 1, 2016 4:05 PM
  • Let's try forcing the value to be a string: the code is looking for the 121st worksheet, which does not exist. I had interpreted your post about number as being along the lines of 1, 2, 3 meaning the first, second, third sheets....

    Dim vName As String 'Changed here

    vName = ActiveWorkbook.Worksheets("Checklist").Range("E5").Value

    For i = 2 To Workbooks.Count If Workbooks(i).Name <> ActiveWorkbook.Name Then Workbooks(i).Activate

    Workbooks(i).Worksheets(vName).Activate Exit For End If Next i



    Monday, February 1, 2016 4:18 PM
  • Excellent! Works perfectly. Thanks.
    Monday, February 1, 2016 4:23 PM