runtime error 1004 paste method of worksheet class failed

  • Thread starter Thread starter wilsoj
  • Start date Start date
W

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
 
Are you getting the error only when your copy source book is open. Try
closing that workbook and re-run the macro.

Mangesh
 
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
 
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!
 
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
 
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
 
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
 
Hi,

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

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

Rgds, Jan
 
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
 
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
 
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
 
Back
Top