I'm trying to copy constant cells from one worksheet to another (a
horrible task in itself, who knew it would be so complicated), and I've
hit a problem. Sometimes, I'm seeing constants entered into cells as
(for example) "=12345" instead of "12345", so Excel thinks it's a
formula. Therefore, these cells aren't getting picked up by
SpecialCells(xlCellTypeConstants). Anyone have any bright ideas?
(Apart from copying a cell at a time - some of our worksheets are huge)

Cheers


Nate

Nate,
Copy required rangePaste Special (Values).

NickHK

"Nate" wrote in message
oups.com...
I'm trying to copy constant cells from one worksheet to another (a
horrible task in itself, who knew it would be so complicated), and I've
hit a problem. Sometimes, I'm seeing constants entered into cells as
(for example) "=12345" instead of "12345", so Excel thinks it's a
formula. Therefore, these cells aren't getting picked up by
SpecialCells(xlCellTypeConstants). Anyone have any bright ideas?
(Apart from copying a cell at a time - some of our worksheets are huge)

Cheers


Nate

Hi Nick,

I'm not using Selection.Copy / PasteSpecial (as far as I know I can't),
as I get the error message "That command cannot be used on multiple
selections" on the Selection.Copy statement, due to the SpecialCells
range not being contiguous (I think).

Nate,
If you cannot take a whole contiguous, you can do it yourself with:
Private Sub CommandButton1_Click()
Dim i As Long

With Selection
For i = 1 To .Areas.Count
.Areas(i).Copy
.Areas(i).PasteSpecial (xlValues)
Next
End With
End Sub

NickHK

"Nate" wrote in message
ups.com...
Hi Nick,

I'm not using Selection.Copy / PasteSpecial (as far as I know I can't),
as I get the error message "That command cannot be used on multiple
selections" on the Selection.Copy statement, due to the SpecialCells
range not being contiguous (I think).

Hi Nate,

I have only one idea how to select constats entered as =12345, but it
will lead to really slow code. You can programmaticaly check each cell
for precedents, if it has no precedents, it is constant (it will
recognize as constants also =1+2 - which in fact is constant).

Regards,
Ivan

I don't see how PasteSpecial Values addresses the problem you stated:

Therefore, these cells aren't getting picked up by
SpecialCells(xlCellTypeConstants).

--
Regards,
Tom Ogilvy


"Nate" wrote:

Hi Nick,

I'm not using Selection.Copy / PasteSpecial (as far as I know I can't),
as I get the error message "That command cannot be used on multiple
selections" on the Selection.Copy statement, due to the SpecialCells
range not being contiguous (I think).

Ah, that's a good point; while it may be too slow to consider here,
I'll file it away for future use - thanks!

I didn't know you could do that, many thanks!

Hi Nate,

there is a way how to speed it up:

You can select constants with specialcells and union this range with
second range, which is - from specialcells(xlcelltypeformulas) you
leave all with precedents.

Regards,
Ivan