PasteSpecial Error

[Otto Moehrbach]

Excel 2002, 2003
I wrote a rather long program for an OP and he distributed it to about a
dozen branch offices in his state. Two of those offices are getting an
error in the PasteSpecial line below. The error is "PasteSpecial method of
range class failed."
The pertinent 2 lines of code are shown below. Note that "GreenButton" and
"Dest" are both one-cell ranges.
GreenButton.Offset(, 1).Resize(, 2).Copy
Dest.PasteSpecial xlPasteValues
The purpose I had in using "xlPasteValues" was to NOT copy the formatting.

Both of the problem computers have 2003 but so do most of the others. I
have 2002.
My thinking is that the problem lies in the setup of Excel on those 2
computers.
Any help would be appreciated. Thanks for your time. Otto

 

[Joel]

You may be extedning beyond the number of columns in the worksheet



if Greenbutton.column + Greenbutton.columns - 1 <= (columns.count - 3) then
GreenButton.Offset(, 1).Resize(, 2).Copy
if Dest.Column + Greenbutton.columns - 1 <= columns.count then
Dest.PasteSpecial xlPasteValues
end if
end if

 

[Otto Moehrbach]

Joel
Thanks for that but the used range covers only about 20 columns. Also,
from my initial post, this program is running on about 10 computers
statewide and only 2 of them are producing this error. Otto

 

[Joel]

I can't tell from the code what GreenButton and Dest are set to. If you are
using set c = FIND() the options may not be set properly. Find in VBA
depends on the FIND on the worksheet settings. You may want to check the
find options on the worksheet to make sure it is set the same on all PC's.

The recomendations are to set all options in the VBA code - lookin, lookat,
and matchcase.

You may want to add a message box for debugging
msgbox("Addr : " & Dest.address & vbcrlf & "Rows : " & Dest.rows & vbcr4lf & _
"Columns : " & Dest.columns)

 

[Otto Moehrbach]

Joel
Greenbutton is any one of a number of green cells and a
Worksheet_SelectionChange macro takes action when one of those cells is
selected (clicked on). Dest is set to the first empty cell in a range of 10
cells. There is no "FIND" in this code. I had the 2 problem computer users
to run additional code as you suggest to validate GreenButton and Dest.
There is no problem with that. Thanks for your time. Otto

 

[Joel]

Can the users modify the cells manually (entire range). Maybe it is a
priledge problem. right now I'm not sure if it is a clipboard problem of a
paste problem. The clipboard can ve view by going to worksheet menu View -
Task Pane. then on taskpane select the down arrow and select clipboard. The
last item that was copied should be at the top and you can manually select
the item and then try pasting in a new workbook. This may help isolate where
the problem is located.