Copy Range From External Workbook to Range in Open Workbook

[vodkasoda]

Hi all, again ... Hopefully a quickie ... I want to access some data in another spreadsheet to the one I have open & where my VBA code is running. I then want to paste that data to a Range on a specific sheet within my open workbook.

Fairly straighforward & simple I would have thought, but as usual I can't get it to work !!! I am getting the error message ...

"Run-time error '1004'; Copy method of Range class failed"

... when executing the following code :

Public MyFixtures As Variant
Public MyFixturesData As Workbook
Public MyRangeFrom As Range
Public MyRangeTo As Range

MyFixtures = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please Select Your Fixtures File")
Stop
If MyFixtures = False Then
Exit Sub
Else
'Application.ScreenUpdating = False
Set MyRangeTo = Sheets("Fixtures").Range("A1:C500")
MyRangeTo.Delete
Set MyFixturesData = Workbooks.Open(MyFixtures)
Set MyRangeFrom = Range("A1:C500")
MyRangeFrom.Copy MyRangeTo <<< Error occurs on this line
MyFixturesData.Close False
Application.ScreenUpdating = True
End If

Can anybody please help ?

[mdmackillop]

You cannnot paste to a range you have deleted. To clear the range use

MyRangeTo.Clearcontents


It is not neccessary to do this, as cells will be overwritten by the pasted data.
You only have to specify the top left cell of the target range, so unless you use it later in the code, use

Set MyRangeTo = Sheets("Fixtures").Range("A1")

[vodkasoda]

mdmackillop ... thank you, it worked perfectly first time. Being a COBOL programmer by trade, I love the diversity and the incredible versatility that languages like VB give me, but I sometimes wonder if they deliberately made some of the simpler things as obscure as possible !!!

I also think it would have been an idea to spend more than 5 minutes on the Error Messages !!!

Thanks again ...



You cannnot paste to a range you have deleted. To clear the range use

MyRangeTo.Clearcontents


It is not neccessary to do this, as cells will be overwritten by the pasted data.
You only have to specify the top left cell of the target range, so unless you use it later in the code, use

Set MyRangeTo = Sheets("Fixtures").Range("A1")