VB Error: Paste Special method of range class failed
 

I have written the following in VB to copy and paste data
from one workbook to another:

ActiveSheet.Range("A6:A35").Select
Selection.Copy
Windows("Data_2004.xls").Activate
Sheets("My Data").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

For some reason everytime I run the Macro I get the
following error:

'Paste Special method of Range class failed'

Can anyone help with this problem??
Cheers
Chris

VB Error: Paste Special method of range class failed
 

Chris,

Works alright here.

You can reduce that code down to:
Range("A6:A35").Copy
Sheets("My Data").Range("A6").PasteSpecial xlPasteValues


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Chris" wrote in message
...
I have written the following in VB to copy and paste data
from one workbook to another:

ActiveSheet.Range("A6:A35").Select
Selection.Copy
Windows("Data_2004.xls").Activate
Sheets("My Data").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

For some reason everytime I run the Macro I get the
following error:

'Paste Special method of Range class failed'

Can anyone help with this problem??
Cheers
Chris

VB Error: Paste Special method of range class failed
 

If the code is contained in a worksheet module, then

Range("A6").Select

refers to the sheet containing the code, not necessarily the active sheet
and would cause such an error.

Assuming the Activesheet at the start of the code is not in Data_2004.xls,
then Rob's suggestion fix should be:

Range("A6:A35").Copy
Workbooks("Data_2004.xls").Sheets("My Data") _
.Range("A6").PasteSpecial xlPasteValues


or you could just do
Workbooks("Data_2004.xls").Sheets("My Data") _
.Range("A6").Resize(30,1).Value = Range("A6:A35").Value

--
Regards,
Tom Ogilvy

"Chris" wrote in message
...
I have written the following in VB to copy and paste data
from one workbook to another:

ActiveSheet.Range("A6:A35").Select
Selection.Copy
Windows("Data_2004.xls").Activate
Sheets("My Data").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

For some reason everytime I run the Macro I get the
following error:

'Paste Special method of Range class failed'

Can anyone help with this problem??
Cheers
Chris