runtime error 1004 paste method of worksheet class failed

[wilsoj]

Hi,

Very new to VBA and having trouble with a simple macro running in 2003
that copies and pasts to another worksheet. Code as follows:

Private Sub CopyResults_Click()
'Select the filtered data and copy it
Range("A23").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F19").Select
' Open the template and copy in the data
Workbooks.Open Filename:="C:\Athens Verification
Data\Templates\Verification Template.xls"
Workbooks("Verification Template.xls").Activate
ActiveSheet.Range("A1").Select
*ActiveSheet.Paste*
ActiveSheet.Range("A1").Select
Application.CutCopyMode = False
End Sub

When I run CopyResults, the sheet is opened and the data copied
correctly, but I get a runtime error 1004 past method of worksheet
class failed, with the line 'ActiveSheet.Paste' highlighted. Don't get
this when I run the same macro in 2000.

Have looked for a solution online but no joy. Be grateful for any
advice!

Many thanks,

Jane

 

[mangesh_yadav]

try ActiveSheet.PasteSpecial

Mangesh

 

[mangesh_yadav]

Are you getting the error only when your copy source book is open. Try
closing that workbook and re-run the macro.

Mangesh

 

[mangesh_yadav]

Try this:


Private Sub CopyResults_Click()
'Select the filtered data and copy it
Workbooks.Open Filename:="C:\Athens Verification
Data\Templates\Verification Template.xls"
Workbooks("Book1.xls").Activate
Range("A23").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Workbooks("Verification Template.xls").Activate
Range("A1").Select
Selection.PasteSpecial
ActiveSheet.Range("A1").Select
Application.CutCopyMode = False
End Sub




Mangesh

 

[wilsoj]

Hi Mangesh,

Thanks for the advice. Have tried PasteSpecial and doesn't resolve it
Tried running your code and get runtime error 9 subscript out of range


Unfortunately, the sub-routine is set up in the copy source book. A
you can guess, really am new to VBA so apologies if I'm missin
something really obvious here!

 

[mangesh_yadav]

I am sorry, I left something unchanged. Try again:

Private Sub CopyResults_Click()
'Select the filtered data and copy it
Workbooks.Open Filename:="C:\Athens Verificatio
Data\Templates\Verification Template.xls"
Workbooks("Verification Template.xls").Activate
Range("A23").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Workbooks("Verification Template.xls").Activate
Range("A1").Select
Selection.PasteSpecial
ActiveSheet.Range("A1").Select
Application.CutCopyMode = False
End Sub


Please check the code for your file names and folders.

Manges

 

[Tom Ogilvy]

Private Sub CopyResults_Click()
Dim rng as Range, sh as Worksheet
'Select the filtered data and copy it
set rng = Range("A23")
set rng = Range(rng, rng.End(xlToRight))
set rng =Range(rng, rng.End(xlDown))
' Open the template and copy in the data
Workbooks.Open Filename:="C:\Athens Verification Data\Templates\Verification
Template.xls"
set sh = ActiveSheet
rng.copy Destination:=sh.Range("A1")
sh.range("A1").Select
End Sub

 

[wilsoj]

Sorted!! That's brill - many thanks.

 

[mangesh_yadav]

Glad its working. Thanks for the feedback.

Manges

 

[Dave Peterson]

Sometimes opening workbooks can empty the clipboard. Maybe if you do things in
a slightly different order, things will be ok:

Option Explicit
Private Sub CopyResults_Click()
Dim curWks As Worksheet
Dim templWks As Worksheet
Dim rngToCopy As Range

Set curWks = ActiveSheet
With curWks
Set rngToCopy = .Range("a23", .Range("a23").End(xlToRight).End(xlDown))
End With

Workbooks.Open _
Filename:="C:\Athens Verification Data\" _
& "Templates\Verification Template.xls"

Set templWks = ActiveSheet

rngToCopy.Copy _
Destination:=templWks.Range("a1")

Application.CutCopyMode = False

End Sub

 

[wilsoj]

Hi,

Many thanks to Tom and Dave. Both do the trick.

Clear that I still have a lot to learn...

Rgds, Jan

 

[shashikiran_g]

Hi,
i tried copying Contents from a web application to an excel.
here i am providing a button to paste the contents that is in
Clipboard
while clicking the button i am getting this run time error at the first
time after that id i again copy the contents from the web application it
is not giving any error
i even tried pastespecial it also didn't work

please can any one suggest some ideas

 

[Guest]

I have had the same problem. What I am trying to do is copy the results of a
subtotal and when I try the below it copies everything. How can I just copy
the totals from the subtotal method?

Before I would do something like this:
Range(Cells(b, "A"), Cells(c, "B")).select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Tables").Select
Range("G1").Select
ActiveSheet.Paste

 

[Jithu]

Hi..
I'm trying to copy a webpage content to excel and I get the same error -Error 1004 -PasteSpecial method of worksheet failed. Below is the piece of code. Can someone help.

For Each ele In IE.Document.all
IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

ActiveSheet.PasteSpecial Paste:=xlPasteValues
Range("A1").Select

Next ele