Hello,

I have 2 separate excel files. I want to create a macro in 1st file
which will open the 2nd file, copy the data from the 2nd file and
paste it in the worksheet of the 1st file in a desired location.

My question is: Because the users of these excel files will have the
two files in the any directory, the Macro I designed looks for a
specific directory on the computer where the 2nd file is. Is there I
way that the directory doesn't play a part in where it looks for the
file? I would like to make the users create 1 directory called HRCL
anywhere on their computer and allow the macro to look for the file in
that specific directory. I want the user to have the option to
create the folder and have the files anywhere... as long as it is in a
subfolder called HRCL

This is the way my macro looks so far
Sub opendata2()
'
' opendata2 Macro
' Macro recorded 07/11/2007 by
'

'
ChDir "C:\Documents and Settings\CHDIMA\Desktop\HRCL"
Workbooks.Open Filename:= _
"C:\Documents and Settings\CHDIMA\Desktop\HRCL\HCRL_data.xls"
Range("A2:BD1000").Select
Selection.Copy
Windows("summaryreport-pivot.xls").Activate
Sheets("ZIRN-data").Select
ActiveSheet.Paste
End Sub

ANy help would be appreciated

On Nov 7, 4:16 pm, wrote:
Hello,

I have 2 separate excel files. I want to create a macro in 1st file
which will open the 2nd file, copy the data from the 2nd file and
paste it in the worksheet of the 1st file in a desired location.

My question is: Because the users of these excel files will have the
two files in the any directory, the Macro I designed looks for a
specific directory on the computer where the 2nd file is. Is there I
way that the directory doesn't play a part in where it looks for the
file? I would like to make the users create 1 directory called HRCL
anywhere on their computer and allow the macro to look for the file in
that specific directory. I want the user to have the option to
create the folder and have the files anywhere... as long as it is in a
subfolder called HRCL

This is the way my macro looks so far
Sub opendata2()
'
' opendata2 Macro
' Macro recorded 07/11/2007 by
'

'
ChDir "C:\Documents and Settings\CHDIMA\Desktop\HRCL"
Workbooks.Open Filename:= _
"C:\Documents and Settings\CHDIMA\Desktop\HRCL\HCRL_data.xls"
Range("A2:BD1000").Select
Selection.Copy
Windows("summaryreport-pivot.xls").Activate
Sheets("ZIRN-data").Select
ActiveSheet.Paste
End Sub

ANy help would be appreciated

SOLUTION:

ChDir CStr(ActiveWorkbook.Path)

works like a charm!!!