Open a file

[Angeliki]

Hello

I would like to copy and paste info from one workbook to another.
However i want to be able to choose the file from where i am going to copy
the values.
the problem is that the file could be in any subdirectory (it's not one file
there are many files
and i want to be able to choose its time which one i am going to use). If i
achieve to have both files open
(the one that i will copy the values and the one i will paste(this is
constant)) then i know the code
But i want you to help me how to open any file i want to copy values

Sub Macro4()
ChDir "E:\Folder1\Folder 2\Workbook1.xls"
Workbooks.Open Filename:= _
"E:\Folder1\Folder 2\Workbook1.xls"
Windows("New Model_1j.xls").Activate
Range("A1").Select
End Sub

Workbook1 (can be in any subdirectory but it has the same name ) = copy
values
New Model (same every time) = paste values

I record a macro how to open a file but i have to write a secific file

Is it possible t choose each time what to use?

Thanks in advance

Angeliki

 

[Patrick Molloy]

read HELP on Application.GetOpenFilename

The following frame will help to get you started.

Sub GetFiles()
Dim sCopyFrom As String, wbFrom As Workbook
Dim sCopyTo As String, wbTo As Workbook
sCopyFrom = Application.GetOpenFilename()
sCopyTo = Application.GetOpenFilename()

Set wbFrom = Workbooks.Open(sCopyFrom)
Set wbTo = Workbooks.Open(sCopyTo)

' copy some data


wbFrom.Close False
wbTo.Close True ' true = Save

Set wbFrom = Nothing
Set wbTo = Nothing

End Sub

befor GetOpenFileName method is called, you may set the
drive & target folder as per your code using ChDrive and
ChDir methods.

The notes in Help will show you how to set the filter for
just EXcel Files...
sCopyFrom = _
Application.GetOpenFilename("Excel Workbooks _
(*.xls),*.xls")

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hello

I would like to copy and paste info from one workbook to another.
However i want to be able to choose the file from where i am going to copy
the values.
the problem is that the file could be in any

subdirectory (it's not one file