Thanks Ron
But for some reason my copy command is not looking in the new file.
It copes the range of the calling worksheet.
Pal
Private Sub CommandButton1_Click()
Dim FName As Variant
Dim wb As Workbook
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.wks),
*.wks")
If FName < False Then
Set wb = Workbooks.Open(FName)
Range("A12:C100").Copy
wb.Close
End If
End Sub
"Ron de Bruin" wrote in message
...
Hi Pal
GetOpenFilename don't open the file (see the Excel help)
You need to open the file
Workbooks.Open(FName)
Sub test()
Dim FName As Variant
Dim wb As Workbook
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls")
If FName < False Then
Set wb = Workbooks.Open(FName)
MsgBox "your code"
wb.Close
End If
End Sub
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl
"Pal" wrote in message
news:Cmo0c.423198$I06.4711213@attbi_s01...
Hi
I have the following code that SHOULD open a file
and copy data from range a12:c100 of that opened file into the calling
worksheet at sheet1 A38.
I get a subscript out of range error trying to activate the newly opened
file.
I passed the file name to the msgbox and found it has the directory path
in
it.
Since I am on a network is there a way to remove that path so any user
at
different
directories can open the file?
Thanks
Pal
Private Sub CommandButton1_Click()
Filename = Application.GetOpenFilename
Windows(Filename).Activate
Range("A12:C100").Select
Selection.Copy
Windows("results sheet").Activate
Range("A38").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
End Sub