Can not run Macro written in Excel 2003 on Excel 2002

Hi all,

I have recorded the following Macro ....

Sub Paste_TSR()
'
' Paste_TSR Macro
' Macro recorded 27/01/2005 by WayneF
'

'
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Basically it just paste's the clip board content's to the given position
with out pasting this formulas. The problem is when I try to use this file
in Excel 2000 or Excel 2002 I get this error ...

Run-time error '1004':
PasteSpecial method of Range class failed

When I go to the debug the following selection is highlighted ...

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

I guess its some kind of compatibility problem ... Can anyone help me to
over come this.

I hope I have been precise enough

Cheers,

Wayne

Wayne

As written your macro will not work in either 2003 or 2002 for me.

You need something to paste.

Add Selection.Copy before the first Selection.PasteSpecial line.


Gord Dibben Excel MVP

On Thu, 27 Jan 2005 16:56:58 +0100, "WayneF" <[email protected]> wrote:

>
> Hi all,
>
> I have recorded the following Macro ....
>
>Sub Paste_TSR()
>'
>' Paste_TSR Macro
>' Macro recorded 27/01/2005 by WayneF
>'
>
>'
> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
> Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>SkipBlanks _
> :=False, Transpose:=False
>End Sub
>
> Basically it just paste's the clip board content's to the given position
>with out pasting this formulas. The problem is when I try to use this file
>in Excel 2000 or Excel 2002 I get this error ...
>
> Run-time error '1004':
> PasteSpecial method of Range class failed
>
> When I go to the debug the following selection is highlighted ...
>
> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
>
> I guess its some kind of compatibility problem ... Can anyone help me to
>over come this.
>
> I hope I have been precise enough
>
> Cheers,
>
> Wayne
>
>
>
>

Wayne,

This was a bug in XL2000/02, where xlPasteColumnWidths was never defined.
Instead, you need to use the hardcoded value 8 as the option to pastespecial

HTH,
Bernie
MS Excel MVP

"WayneF" <[email protected]> wrote in message
news:[email protected]...
>
> Hi all,
>
> I have recorded the following Macro ....
>
> Sub Paste_TSR()
> '
> ' Paste_TSR Macro
> ' Macro recorded 27/01/2005 by WayneF
> '
>
> '
> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
_
> SkipBlanks:=False, Transpose:=False
> Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> End Sub
>
> Basically it just paste's the clip board content's to the given
position
> with out pasting this formulas. The problem is when I try to use this file
> in Excel 2000 or Excel 2002 I get this error ...
>
> Run-time error '1004':
> PasteSpecial method of Range class failed
>
> When I go to the debug the following selection is highlighted ...
>
> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
_
> SkipBlanks:=False, Transpose:=False
>
> I guess its some kind of compatibility problem ... Can anyone help me
to
> over come this.
>
> I hope I have been precise enough
>
> Cheers,
>
> Wayne
>
>
>
>
>

Hi Gord,

I dont want to define the copy part, The user should go and start the
copy and then position the cursor at the paste area and just hit the button.
The I want the values and NOT the formula pasted.

P.S. It works fine in 2003 just not in porevios version

Thanks for your help !

Cheers,

Wayne

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> Wayne
>
> As written your macro will not work in either 2003 or 2002 for me.
>
> You need something to paste.
>
> Add Selection.Copy before the first Selection.PasteSpecial line.
>
>
> Gord Dibben Excel MVP
>
> On Thu, 27 Jan 2005 16:56:58 +0100, "WayneF" <[email protected]>
> wrote:
>
>>
>> Hi all,
>>
>> I have recorded the following Macro ....
>>
>>Sub Paste_TSR()
>>'
>>' Paste_TSR Macro
>>' Macro recorded 27/01/2005 by WayneF
>>'
>>
>>'
>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
>> _
>> SkipBlanks:=False, Transpose:=False
>> Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
>> SkipBlanks:=False, Transpose:=False
>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>>SkipBlanks _
>> :=False, Transpose:=False
>>End Sub
>>
>> Basically it just paste's the clip board content's to the given
>> position
>>with out pasting this formulas. The problem is when I try to use this file
>>in Excel 2000 or Excel 2002 I get this error ...
>>
>> Run-time error '1004':
>> PasteSpecial method of Range class failed
>>
>> When I go to the debug the following selection is highlighted ...
>>
>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
>> _
>> SkipBlanks:=False, Transpose:=False
>>
>> I guess its some kind of compatibility problem ... Can anyone help me
>> to
>>over come this.
>>
>> I hope I have been precise enough
>>
>> Cheers,
>>
>> Wayne
>>
>>
>>
>>
>

Cheers Bernie,

Your years of hard work have payed of for me

Thanks a lot for your help, Just got it working a treat !

Cheers,

Wayne



"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Wayne,
>
> This was a bug in XL2000/02, where xlPasteColumnWidths was never defined.
> Instead, you need to use the hardcoded value 8 as the option to
> pastespecial
>
> HTH,
> Bernie
> MS Excel MVP
>
> "WayneF" <[email protected]> wrote in message
> news:[email protected]...
>>
>> Hi all,
>>
>> I have recorded the following Macro ....
>>
>> Sub Paste_TSR()
>> '
>> ' Paste_TSR Macro
>> ' Macro recorded 27/01/2005 by WayneF
>> '
>>
>> '
>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
> _
>> SkipBlanks:=False, Transpose:=False
>> Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
>> SkipBlanks:=False, Transpose:=False
>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>> SkipBlanks _
>> :=False, Transpose:=False
>> End Sub
>>
>> Basically it just paste's the clip board content's to the given
> position
>> with out pasting this formulas. The problem is when I try to use this
>> file
>> in Excel 2000 or Excel 2002 I get this error ...
>>
>> Run-time error '1004':
>> PasteSpecial method of Range class failed
>>
>> When I go to the debug the following selection is highlighted ...
>>
>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
> _
>> SkipBlanks:=False, Transpose:=False
>>
>> I guess its some kind of compatibility problem ... Can anyone help me
> to
>> over come this.
>>
>> I hope I have been precise enough
>>
>> Cheers,
>>
>> Wayne
>>
>>
>>
>>
>>
>
>

Gotcha.

I read it as if you were just copying in place.

I see Bernie's zeroed in on the problem.


Gord

On Thu, 27 Jan 2005 19:00:42 +0100, "WayneF" <[email protected]> wrote:

> Hi Gord,
>
> I dont want to define the copy part, The user should go and start the
>copy and then position the cursor at the paste area and just hit the button.
>The I want the values and NOT the formula pasted.
>
> P.S. It works fine in 2003 just not in porevios version
>
> Thanks for your help !
>
> Cheers,
>
> Wayne
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:[email protected]...
>> Wayne
>>
>> As written your macro will not work in either 2003 or 2002 for me.
>>
>> You need something to paste.
>>
>> Add Selection.Copy before the first Selection.PasteSpecial line.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Thu, 27 Jan 2005 16:56:58 +0100, "WayneF" <[email protected]>
>> wrote:
>>
>>>
>>> Hi all,
>>>
>>> I have recorded the following Macro ....
>>>
>>>Sub Paste_TSR()
>>>'
>>>' Paste_TSR Macro
>>>' Macro recorded 27/01/2005 by WayneF
>>>'
>>>
>>>'
>>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
>>> _
>>> SkipBlanks:=False, Transpose:=False
>>> Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
>>> SkipBlanks:=False, Transpose:=False
>>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>>>SkipBlanks _
>>> :=False, Transpose:=False
>>>End Sub
>>>
>>> Basically it just paste's the clip board content's to the given
>>> position
>>>with out pasting this formulas. The problem is when I try to use this file
>>>in Excel 2000 or Excel 2002 I get this error ...
>>>
>>> Run-time error '1004':
>>> PasteSpecial method of Range class failed
>>>
>>> When I go to the debug the following selection is highlighted ...
>>>
>>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
>>> _
>>> SkipBlanks:=False, Transpose:=False
>>>
>>> I guess its some kind of compatibility problem ... Can anyone help me
>>> to
>>>over come this.
>>>
>>> I hope I have been precise enough
>>>
>>> Cheers,
>>>
>>> Wayne
>>>
>>>
>>>
>>>
>>
>

Fixed in xl2002, actually.

Bernie Deitrick wrote:
>
> Wayne,
>
> This was a bug in XL2000/02, where xlPasteColumnWidths was never defined.
> Instead, you need to use the hardcoded value 8 as the option to pastespecial
>
> HTH,
> Bernie
> MS Excel MVP
>
<<snipped>>