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

try ActiveSheet.PasteSpecial

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376722

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

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376722

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!



--
wilso
-----------------------------------------------------------------------
wilsoj's Profile: http://www.excelforum.com/member.php...fo&userid=2406
View this thread: http://www.excelforum.com/showthread.php?threadid=37672

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

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37672

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" wrote in
message ...

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

Sorted!! That's brill - many thanks.



--
wilso
-----------------------------------------------------------------------
wilsoj's Profile: http://www.excelforum.com/member.php...fo&userid=2406
View this thread: http://www.excelforum.com/showthread.php?threadid=37672

Glad its working. Thanks for the feedback.

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37672

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, Jan

--
wilso
-----------------------------------------------------------------------
wilsoj's Profile: http://www.excelforum.com/member.php...fo&userid=2406
View this thread: http://www.excelforum.com/showthread.php?threadid=37672

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


--
shashikiran_g
------------------------------------------------------------------------
shashikiran_g's Profile: http://www.excelforum.com/member.php...o&userid=24180
View this thread: http://www.excelforum.com/showthread...hreadid=376722

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