Error on pasting

argenta

Board Regular
Joined
Jan 10, 2004
Messages
60
I'm trying to copy some cells from one worksheet and paste them in another worksheet in another workbook. But I get the error message :-

Run-time error -2147417848 (80010108)
Method 'Paste of object '_Worksheet' failed.

The code I'm using is :-

Code:
FilePath = ActiveWorkbook.Path & "\Spirit Supply Reports.xls"
Workbooks.Open Filename:=FilePath
Workbooks("Spirit Supply Scheduler.xls").Activate
Worksheets("Parameters").Range(Cells(1, 1), Cells(3, 2)).Copy
Workbooks("Spirit Supply Reports.xls").Activate
Worksheets("Parameters").Activate
Cells(1, 1).Activate
ActiveSheet.Paste

This is very similar to a method I'm using elsewhere but this doesn't seem to work.

In fact Copy and Paste in VBA seems to me to be very 'Hit and Miss'. So, as well as a fix to this particular problem, I would be interested in general guidelines.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
If you use code correctly you will not have (so many :biggrin:) problems.

Excel VBA has extensive help. One of the most useful being the facility of putting the cursor into a word and pressing the F1 key. So if you type the word 'Paste' and press F1 .. voila !

As you have discovered Paste (in its own line of code) has a very wierd code requirement. I call this "belt and braces" because we have to include the worksheet name twice. So, in essence, your code becomes :-

Code:
ActiveSheet.Paste Destination:=Worksheets("Parameters").Range("A1")

This is the way I would do it :-
Code:
    Dim FromSheet As Worksheet
    Dim ToSheet As Worksheet
    '----------------------------------
    Set FromSheet = Workbooks("Spirit Supply Scheduler.xls").Worksheets("Parameters")
    Set ToSheet = Workbooks("Spirit Supply Report.xls").Worksheets("Parameters")
    FromSheet.Range(Cells(1, 1), Cells(3, 2)).Copy _
        Destination:=ToSheet.Range("A1")
 

argenta

Board Regular
Joined
Jan 10, 2004
Messages
60
Brian

Thanks very much for that, it's certainly beginning to throw some light on it.

However, when I try to run your code exactly as you have defined it I get the following error on the copying line :-

Run-time error '1004'

Method 'Range' of object '_Worksheet' failed.

Again this is something I've come across before and never really understood why it was a problem.

In reality I want to copy the whole sheet. What's the best way of doing that?

Code:
    FromSheet.Cells.Copy _
        Destination:=ToSheet.Range("A1")



Thenks

Jeff
 

argenta

Board Regular
Joined
Jan 10, 2004
Messages
60
I've done some more investigation and, in fact the method works fine except if the FromSheet contains cells or ranges with Names associated with them.

So if I copy a single cell which is not associated with a Name it works fine. If I try to copy a cell associated with a Name it comes up with an Automation error.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Maybe try qualifying the Cells property too:

FromSheet.Range(FromSheet.Cells(1, 1), FromSheet.Cells(3, 2)).Copy _
Destination:=ToSheet.Range("A1")

If you don't Excel tries to use the ActiveSheet, which may not be the same as FromSheet, causing an error.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
In reality I want to copy the whole sheet. What's the best way of doing that?

Again, the answer is in Excel VB Editor Help. something like :-

Workbooks("Book1.xls").WorkSheets("Sheet1").Copy After:=WorkBooks("Book2.xls").Worksheets(1)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,088
Members
412,310
Latest member
mark884
Top