Updating macro to Excel 2010

[papillon]

I have a macro which was developed in Excel 2007 (version 12.0?). When I run it in Excel 2010 (version 14.0), I get some inconsistent errors as follows:

I have this code operating on one worksheet:

Range("A1").Select
ActiveSheet.Paste
which is acceptable. A little later, I have, operating on a different worksheet:

Range("A6").Select
ActiveSheet.Paste
which gives a run-time error '1004': Paste method of Worksheet class failed.

Commenting that statement out and running again. I have this code:

Cells.Find(What:="3", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) _
.Activate

which runs OK. A few lines further on:

Cells.Find(What:="6", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
fails with a run-time error '91': Object variable or width block variable not set.

What's happening and how do I fix it? These appear to be the only sources of run-time error.

I'm running the macro in Windows 7 on an ASUS Eee Slate. I can't test it anywhere else.

[Kenneth Hobs]

There is nothing wrong with the code per se. It does not check for scenarios where it might fail though. How does it handle pasting when some cells might be protected on a protect sheet and workbook? How can it activate a found cell if it was not found. You need to set the find to a range object and then check if it is not Nothing before activating the set range.

[papillon]

Using the same source data, the code runs well in Excel 2007 and fails in Excel 2010. My problem is that two identical commands are accepted on their first encounter by VBA and rejected on their next encounter. How and why?

[Kenneth Hobs]

I would suggest that you make a short example xlsm and post it.