Hi Matt,
what I did, is leave the directory address in a cell, in a worksheet where
your macro is, so that everyone who wants to use your file, have their own
address in that book.
Example:
Your machine:
Cell A4 value: "C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports"
Somebody else:
Cell A4 value: "C\Jorge\Rob"
and in the macro line
Workbooks.Open Filename:= _
ActiveSheet.Range("A4").Value & "Updated Responder Data.xls"
erase this line (not necesary):
ChDir _
"C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports"


You can use a
"Matt" wrote:

I have a question in opening a file from a macro used in Excel. I want the
file open to be flexible no matter what machine the Excel file is opened on,
but not sure how to do it. I've never learned VB in a class or anything and
have been learning it on my own. So, any help would be greatly appreciated.
here is the code I have written thus far (it is a conditional operation):

Sub Macro6()


Sheets("Main Menu").Select

If Range(" T11 ").Value = "2" Then


If Range("W11").Value = "0" Or Range("W11").Value = "1" Or
Range("W11").Value = "3" Then
ChDir _
"C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports\Updated Responder Data.xls"

Sheets("Responders").Visible = True
Sheets("Responders").Select

Range("AU2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AV2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If

If Range("W11").Value = "2" Then
Windows("Updated Responder Data.xls").Activate


Sheets("Responders").Visible = True
Sheets("Responders").Select

Range("AU2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AV2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If

Windows("Master Aggregate Response Report.xls").Activate

Sheets("Response Pivot Table").Visible = True
Sheets("Response Pivot Table").Select
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel,
True
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
Sheets("Response Pivot Table").Visible = False
Sheets("Main Menu").Select
End If

If Range(" T11 ").Value = ("1" Or "3") Then

Sheets("Response Pivot Table").Visible = False

End If

End Sub




So, obviously I don't want it to follow the path as written so that another
person can perform the macro at a different machine....