Hi All,
I have two excel sheets "A" and "B"
In "A" I have list of sheets present in B
When i click on a cell(where worksheet name is present) I have to navigated to the particular sheet.
I have done code, but when the workbook is already open, it is not activating the sheet :(
I want the user to see the sheet which he has clicked (The sheet is in B worksheet)
Pls help.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim DataPath As String
Dim SheetName As String
Dim indexSheetObj As Worksheet
Dim SheetObj As Worksheet
Dim DataWorkbook As Workbook
If Not Intersect(Target, Range("B2:B7")) Is Nothing Then
SheetName = Trim(Target.Value)
DataPath = "C:\B.xls"
'---> This part opens the workbook if it is not already open If Not IsFileOpen(DataPath) Then
Workbooks.Open DataPath
End If
'It navigates to right sheet if it opens freshly.
'But when B workbook is already open, the desired workbook is NOT getting Activated
'I want sheet should to be displayed in the screen
Set DataWorkbook = Application.Workbooks("B")
DataWorkbook.Sheets(SheetName).Activate
End Sub
Gist:
When the sheet is aleady open, it is navigating to the desired sheet.
When it opens new, it navigates to the correct sheet.
Stuck here... Pls help
Thanks,
Guru