Q:

How do I get the selected tab in a workbook (but not the ActiveWorkbook)?

I hope this is a simple question...
My understanding is that "ActiveWorkbook" returns the currently active workbook, even if the macro was run in a different workbook (this is why I almost always use "ThisWorkbook" instead).
And "ActiveSheet" returns the currently active worksheet, even if the macro was run in a different workbook (or different worksheet).
So how can I get the worksheet which currently has focus within a specific workbook, even if that workbook is not the currently active one?

A:

You can do this by fully qualifying .ActiveSheet
Example:
Private Sub test()

    Dim wb As Workbook

    Set wb = Workbooks.Add

    'Change the name of Sheet1 in the second workbook
    'so it's not confused with Sheet1 in the first workbook.
    wb.Sheets("Sheet1").Name = "Foo"

    ThisWorkbook.Activate

    MsgBox wb.ActiveSheet.Name

End Sub

