Prev: Dynamic view of data based on cell content
Next: Macro to Pivot data in each sheet within a workbook
From: ulfb on 14 Apr 2010 09:39
Hi

First step is no problem:
in Sheet1 col AI this formula is inserted with VB to add age groups
=OM(OCH(Age>=0;Age<7);"a. 0-6";OM(OCH(Age>6;Age<16);"b.
7-15";OM(OCH(Age>15;Age<26);"c. 16-25";OM(OCH(Age>25);"d. 26-";""))))
Col is autofilled to last used row.

In next step I loop thru these rows to find certain rows and copy them to
Sheet2. Problem is that formula on each row is then copied. Instead I need it
to be values only!

Needs to work in Excel 2003 and 2007. Sheet1 can have >20 000 rows

Any smart ideas how I should handle this?

Thanks
Ulf
From: ryguy7272 on 14 Apr 2010 10:14
It will be xlPasteValue. So, something like this...

Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ulfb" wrote:

> Hi
>
> First step is no problem:
> in Sheet1 col AI this formula is inserted with VB to add age groups
> =OM(OCH(Age>=0;Age<7);"a. 0-6";OM(OCH(Age>6;Age<16);"b.
> 7-15";OM(OCH(Age>15;Age<26);"c. 16-25";OM(OCH(Age>25);"d. 26-";""))))
> Col is autofilled to last used row.
>
> In next step I loop thru these rows to find certain rows and copy them to
> Sheet2. Problem is that formula on each row is then copied. Instead I need it
> to be values only!
>
> Needs to work in Excel 2003 and 2007. Sheet1 can have >20 000 rows
>
> Any smart ideas how I should handle this?
>
> Thanks
> Ulf
 | 
Pages: 1
Prev: Dynamic view of data based on cell content
Next: Macro to Pivot data in each sheet within a workbook