# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  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.  As you can guess, really am new to VBA so apologies if I'm missing something really obvious here!

J

----------


## 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 Verification 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.

Mangesh

----------


## wilsoj

Sorted!!  That's brill - many thanks.

J

----------


## mangesh_yadav

Glad its working. Thanks for the feedback.

Mangesh

----------


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

--
Regards,
Tom Ogilvy


"wilsoj" <wilsoj.1q7bua_1118055907.4675@excelforum-nospam.com> wrote in
message news:wilsoj.1q7bua_1118055907.4675@excelforum-nospam.com...
>
> 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
>
>
> --
> wilsoj
> ------------------------------------------------------------------------
> wilsoj's Profile:
http://www.excelforum.com/member.php...o&userid=24068
> View this thread: http://www.excelforum.com/showthread...hreadid=376722
>

----------


## 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 wrote:
>
> 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
>
> --
> wilsoj
> ------------------------------------------------------------------------
> wilsoj's Profile: http://www.excelforum.com/member.php...o&userid=24068
> View this thread: http://www.excelforum.com/showthread...hreadid=376722

--

Dave Peterson

----------


## wilsoj

Hi,

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

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

Rgds,  Jane

----------


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

----------


## Dwaine Horton

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

----------


## Esalit

Mangesh if you are reading this how do I fix the runtime error for this code?

Sub countWorksheets()



Dim WorkbookName1, WorkbookName2, SheetName As String

Dim NumSheets1, NumSheets2 As Integer



WorkbookName1 = ActiveWorkbook.Name

NumSheets1 = ActiveWorkbook.Sheets.Count

Workbooks.Add

WorkbookName2 = ActiveWorkbook.Name

NumSheets2 = ActiveWorkbook.Sheets.Count



Do While NumSheets2 < NumSheets1

Worksheets.Add

NumSheets2 = ActiveWorkbook.Sheets.Count

Loop



Windows(WorkbookName1).Activate

For I = 1 To ActiveWorkbook.Sheets.Count

Windows(WorkbookName1).Activate

ActiveWorkbook.Sheets(I).Select

SheetName = ActiveWorkbook.Sheets(I).Name

Cells.Select

Selection.Copy

Windows(WorkbookName2).Activate

ActiveWorkbook.Sheets(I).Select

Cells.Select

Selection.PasteSpecial Paste:=xlValues

Selection.PasteSpecial Paste:=xlFormats

ActiveWorkbook.Sheets(I).Name = SheetName

Next I



Windows(WorkbookName2).Activate

ActiveWorkbook.SaveAs Filename:="c:\Copy" & WorkbookName1



Thank you

----------


## shg

A good place to start is by reading the forum rules, starting your own thread, and enclosing your code in code tags.

Thanks.

----------

