![]() |
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 |
All times are GMT +1. The time now is 11:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com