Macro to open a designated sheet in a separate file

  • Thread starter Thread starter Michael Lanier
  • Start date Start date
M

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
 
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
Click to expand...

Did you try using HYPERLINK()?
 
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
 
Back
Top