Error on pasting

[argenta]

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 :-

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.

 

[BrianB]

If you use code correctly you will not have (so many ) 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 :-

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

This is the way I would do it :-

    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]

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?

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

Thenks

Jeff

 

[argenta]

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]

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]

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)