runtime error 1004 paste method of worksheet class failed

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

try ActiveSheet.PasteSpecial

Mangesh

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. As you can guess, really am new to VBA so apologies if I'm missing something really obvious here!

J

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

Sorted!! That's brill - many thanks.

J

Glad its working. Thanks for the feedback.

Mangesh

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

Hi,

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

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

Rgds, Jane

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

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