Runtime error 424 - Object required

Hello

I am getting the Runtime 424 error with the code below. I am a newbie and have looked at help but cannot understand how I can modify the code below to give me what I wana do.

Purpose of the code:

User selects a file->Open the file->copy over A10 to A10 of the source file

Thanks in advance



Sub test()

Dim file_open As Variant

file_open = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

Workbooks.Open Filename:=file_open

' open the source workbook, read only
With ThisWorkbook.Worksheets("Sheet1")
' read data from the source workbook
.Range("A10").Formula = file_open.Worksheets("PM").Range("A10").Formula


End With
file_open.Close False ' close the source workbook without saving any changes
Set file_open = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Give this a try...

Sub test()

Dim file_open As Variant
Dim wbkDestination as workbook

file_open = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")

Workbooks.Open Filename:=file_open
set wbkDestination = Activeworkbook
' open the source workbook, read only
With ThisWorkbook.Worksheets("Sheet1")
' read data from the source workbook
..Range("A10").Formula =
wbkDestination.Worksheets("PM").Range("A10").Formula


End With
wbkDestination.Close False ' close the source workbook without saving
any changes
Set wbkDestination = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

--
HTH...

Jim Thomlinson


"periro16" wrote:

>
> Hello
>
> I am getting the Runtime 424 error with the code below. I am a newbie
> and have looked at help but cannot understand how I can modify the code
> below to give me what I wana do.
>
> Purpose of the code:
>
> User selects a file->Open the file->copy over A10 to A10 of the source
> file
>
> Thanks in advance
>
>
>
> Sub test()
>
> Dim file_open As Variant
>
> file_open = Application.GetOpenFilename("Excel Files (*.xls),
> *.xls")
>
> Workbooks.Open Filename:=file_open
>
> ' open the source workbook, read only
> With ThisWorkbook.Worksheets("Sheet1")
> ' read data from the source workbook
> .Range("A10").Formula =
> file_open.Worksheets("PM").Range("A10").Formula
>
>
> End With
> file_open.Close False ' close the source workbook without saving
> any changes
> Set file_open = Nothing ' free memory
> Application.ScreenUpdating = True ' turn on the screen updating
> End Sub
>
>
> --
> periro16
> ------------------------------------------------------------------------
> periro16's Profile: http://www.excelforum.com/member.php...o&userid=26345
> View this thread: http://www.excelforum.com/showthread...hreadid=469533
>
>