Excel 2010 code wont copy/paste to values
 

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.

Excel 2010 code wont copy/paste to values
 

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Excel 2010 code wont copy/paste to values
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Excel 2010 code wont copy/paste to values
 

On Feb 16, 3:40*pm, jaxgab wrote:
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 :)