PDA

View Full Version : Solved: Compatibility



debauch
08-03-2007, 06:21 AM
Hello,
We have a tool that removes formula from each workbook we select, and it works great in 97. When we upgraded to 2003 though, we are not able to use the same code. Below is where it errors out (on ws.cells.select)

The message says "Object does not support Method or Object".

For Each ws In ActiveWorkbook.Sheets
ws.Activate
ws.Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next


There is a line that is no longer support in 2003 in here you think?

rory
08-03-2007, 06:25 AM
Is there any protection on the sheet, and is it definitely a worksheet not a chart sheet?
Regards,
Rory

mvidas
08-03-2007, 06:31 AM
I agree with Rory, the issue is likely a chart sheet. You can avoid that by changing .Sheets to .Worksheets

That being said, your routine could be shortened to:Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
WS.Cells.Copy
WS.Cells.PasteSpecial xlValues
Application.CutCopyMode = False
Next

debauch
08-03-2007, 06:41 AM
I'll give that a try. It's weird because the same files work for other employee's with the same add-ins, but 97. Let me try that, and i'll be back.

debauch
08-03-2007, 06:46 AM
Wow. That did work.
Breifly - why is that? There is several files that we remove formulas from , to post to a website for reporting. It always has worked for other files, even these ones in 97. What changed?

mvidas
08-03-2007, 07:04 AM
The Sheets collection refers to any Worksheet or Chart objects in the workbook, whereas the Worksheets refers to only worksheets (similarly, ActiveWorkbook.Charts refers to only the charts). Since there are no cells to select on your chart sheets, the error pops up. This would happen in any version of excel. My guess is one of the users must have just added a chart to a workbook that never had one before, and thats why this routine tripped up. As written, your original code above would work fine if you're sure there are no charts, specifying the Worksheets collection instead ensures it'll work either way :)