Excel 2010 code wont copy/paste to values

[jaxgab]

I have used code like the following for many years to remove all the
formulas from multiple worksheets.

Sub Convert2Values()
Sheets(Array("Cover", "Comments", "Month", "YTD", "Analysis",
"Cost Per Car")). _
Select
Sheets("Cover").Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

The code still works as a stand-alone macro. However, when I place
the place the code into another Excel macro, it will only convert the
first page ("Cover") to values.

Do anyone have any ideas what is going on? I was previously using
Excel 2003 and the code worked fine.

 

[GS]

jaxgab wrote on 2/16/2011 :

I have used code like the following for many years to remove all the
formulas from multiple worksheets.

Sub Convert2Values()
Sheets(Array("Cover", "Comments", "Month", "YTD", "Analysis",
"Cost Per Car")). _
Select
Sheets("Cover").Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

The code still works as a stand-alone macro. However, when I place
the place the code into another Excel macro, it will only convert the
first page ("Cover") to values.

Do anyone have any ideas what is going on? I was previously using
Excel 2003 and the code worked fine.

I'm thinking it has to do with the difference in the number of
rows/cols being too many to fit memory. I suggest you iterate the array
and use UsedRange to set .Value=.Value.

 

[GS]

Here's what works for me:

Sub CopyRangeValues_AllSheets()
Dim sh As Variant

For Each sh In Split("Cover,Comments,Month,YTD,Analysis,Cost Per
Car", ",")
With Sheets(sh).UsedRange
.Value = .Value
End With
Next
End Sub

 

[Harrison Hill]

I have used code like the following for many years to remove all the
formulas from multiple worksheets.

Sub Convert2Values()
    Sheets(Array("Cover", "Comments", "Month", "YTD", "Analysis",
"Cost Per Car")). _
        Select
    Sheets("Cover").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
        :=False, Transpose:=False
End Sub

The code still works as a stand-alone macro.  However, when I place
the place the code into another Excel macro, it will only convert the
first page ("Cover") to values.

Do anyone have any ideas what is going on?  I was previously using
Excel 2003 and the code worked fine.

I bet you wish Lotus 3 DOS was still around - you could address the
"cube" directly and Clear it or Copy to it.

Excel is a pizza menu, whereas Lotus DOS was the Encyclopaedia
Britannica