You look for the file he

C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports\Updated Responder Data.xls

Will the path always be the same? If not will it always be the same except
for the "Rob" portion. If not, then how would you find it manually.

Why do you use spaces like this:
If Range(" T11 ").Value = "2" Then

Just use Range("T1")

toward the bottom you are always testing if Range("T1") = 3

with the code: If Range(" T11 ").Value = ("1" Or "3") Then

if you want to test whether it contains 1 or 3 you must do

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

--
Regards,
Tom Ogilvy





"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....