Macro to open a designated sheet in a separate file

[Michael Lanier]

When I select value 1 in cell A1 from a dropdown list in File 1, I
need a macro that will open Sheet1 of File 2. If I select value 2
from the dropdown list, I want to be taken to Sheet2 of File 2, and so
forth. Can someone offer a suggestion? Thanks.

Michael

 

[GS]

Michael Lanier submitted this idea :

When I select value 1 in cell A1 from a dropdown list in File 1, I
need a macro that will open Sheet1 of File 2. If I select value 2
from the dropdown list, I want to be taken to Sheet2 of File 2, and so
forth. Can someone offer a suggestion? Thanks.

Michael

Did you try using HYPERLINK()?

 

[Gord Dibben]

How far does "and so forth" extend?

There are better methods but here is sheet event code for one file, 2 sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mypath As String
mypath = "C:\Program Files\Microsoft Office\your folder\"
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Select Case Target.Value
Case "1"
Workbooks.Open Filename:=mypath & "File2.xls"
Sheets("Current").Select
Case "2"
Workbooks.Open Filename:=mypath & "File2.xls"
Sheets("Sheet2").Select
End Select
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP