Quickest way to copy then Paste Special-Values in place?
 

All -

I'm cycling through a large range (~25x5300 cells) and setting .formula
= .value to accomplish paste-special-values. There has to be a quicker
way to copy and paste in place.

When I repeat the macro recoder code, it somehow screws up in the
spreadsheet, and I get ghost cells on my screen--cells overlaying cells
that aren't really there.

Anyway, - what's the best way to do this?

....best, Hash

Quickest way to copy then Paste Special-Values in place?
 

Range(yourRange).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues


wrote in message news:T2xQd.65996$2p.48322@lakeread08...
All -

I'm cycling through a large range (~25x5300 cells) and setting .formula
= .value to accomplish paste-special-values. There has to be a quicker
way to copy and paste in place.

When I repeat the macro recoder code, it somehow screws up in the
spreadsheet, and I get ghost cells on my screen--cells overlaying cells
that aren't really there.

Anyway, - what's the best way to do this?

...best, Hash

Quickest way to copy then Paste Special-Values in place?
 

No reason to loop if it is all one range

with Range("A1:Y5300")
.Formula = .Value
End With

--
Regards,
Tom Ogilvy

wrote in message news:T2xQd.65996$2p.48322@lakeread08...
All -

I'm cycling through a large range (~25x5300 cells) and setting .formula
= .value to accomplish paste-special-values. There has to be a quicker
way to copy and paste in place.

When I repeat the macro recoder code, it somehow screws up in the
spreadsheet, and I get ghost cells on my screen--cells overlaying cells
that aren't really there.

Anyway, - what's the best way to do this?

...best, Hash

Quickest way to copy then Paste Special-Values in place?
 

Tom -

Thanks. They were. Works fine except I now need to split it up. One
columnn was read-in as text in a date format that Excel now turns into a
date-number. Comes in as 2005-02-16 and becomes 2/16/05. Changing the
number format doesn't hack it. I need it to stay as text.

..Formula = Format(.value, "yyyy-mm-dd") gives a type mismatch.

Will .Formula = "='" & .value work? Or do I have to pass .value to an
intermediary Date variable? or some 3rd choice? I'll accept some
overhead here to stay as a text-string.

....best, Hash

In article ,
"Tom Ogilvy" wrote:

No reason to loop if it is all one range

with Range("A1:Y5300")
.Formula = .Value
End With

Quickest way to copy then Paste Special-Values in place?
 

Steve -

That's the code that gave the ghosting. Don't have a clue as to why,
but it was repeatable for this size array. Didn't happen on smaller
arrays used as guinea pigs for the macro recorder.

Thanks for chiming in.

....best, Hash

In article ,
"Steve" wrote:

Range(yourRange).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues


wrote in message news:T2xQd.65996$2p.48322@lakeread08...
All -

I'm cycling through a large range (~25x5300 cells) and setting .formula
= .value to accomplish paste-special-values. There has to be a quicker
way to copy and paste in place.

When I repeat the macro recoder code, it somehow screws up in the
spreadsheet, and I get ghost cells on my screen--cells overlaying cells
that aren't really there.

Anyway, - what's the best way to do this?

...best, Hash

Quickest way to copy then Paste Special-Values in place?
 

Think your back to looping.

--
Regards,
Tom Ogilvy


wrote in message news:EnRQd.68019$2p.4743@lakeread08...
Tom -

Thanks. They were. Works fine except I now need to split it up. One
columnn was read-in as text in a date format that Excel now turns into a
date-number. Comes in as 2005-02-16 and becomes 2/16/05. Changing the
number format doesn't hack it. I need it to stay as text.

.Formula = Format(.value, "yyyy-mm-dd") gives a type mismatch.

Will .Formula = "='" & .value work? Or do I have to pass .value to an
intermediary Date variable? or some 3rd choice? I'll accept some
overhead here to stay as a text-string.

...best, Hash

In article ,
"Tom Ogilvy" wrote:

No reason to loop if it is all one range

with Range("A1:Y5300")
.Formula = .Value
End With

Quickest way to copy then Paste Special-Values in place?
 

Tom -

Looping it was. And a custom number format. I needed the end result to
be a tabbed text file. Saving as text saved with it the custom format,
something I had't appreciated.

Thanks for your help.

....best, Hash

In article ,
"Tom Ogilvy" wrote:

Think your back to looping.

--
Regards,
Tom Ogilvy


wrote in message news:EnRQd.68019$2p.4743@lakeread08...
Tom -

Thanks. They were. Works fine except I now need to split it up. One
columnn was read-in as text in a date format that Excel now turns into a
date-number. Comes in as 2005-02-16 and becomes 2/16/05. Changing the
number format doesn't hack it. I need it to stay as text.

.Formula = Format(.value, "yyyy-mm-dd") gives a type mismatch.

Will .Formula = "='" & .value work? Or do I have to pass .value to an
intermediary Date variable? or some 3rd choice? I'll accept some
overhead here to stay as a text-string.

...best, Hash

In article ,
"Tom Ogilvy" wrote:

No reason to loop if it is all one range

with Range("A1:Y5300")
.Formula = .Value
End With