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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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)
 

Forum statistics

Threads
1,171,705
Messages
5,877,034
Members
433,228
Latest member
Sarah1989

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top