Macro to open a designated sheet in a separate file

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
 
G

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()?
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top