Runtime error 424 - Object required 1

[kalalala]

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

 

[PHV]

Sub test()
Dim file_open As Variant
Dim WB As Workbook
file_open = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Set WB = Workbooks.Open Filename:=file_open
With ThisWorkbook.Worksheets("Sheet1")
' read data from the source workbook
.Range("A10").Formula = WB.Worksheets("PM").Range("A10").Formula
End With
WB.Close False ' close the source workbook without saving any changes
Set WB = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kalalala]

compile error - syntax error on this line:

Set WB = Workbooks.Open Filename:=file_open

??

 

[PHV]

OOps, sorry for the typo:
Set WB = Workbooks.Open(Filename:=file_open)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886