PDA

View Full Version : Remove formula / Hardcode Data



demetre
02-07-2008, 02:31 AM
Good morning Everyone

I have an issue where I want to hardcode values in cells which were originally formula based. I normally use the following code, but sometimes it does not work (i do not know why though...)


With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With


Is there a better method which will force excel to remove the formula, and instead hardcode the data?

One other question, I have implemented a method where I extract mutiple (~600 worksheets at a time) from different type worksheets into one Master worksheet. The problem I am having is that 2-3 of them will not extract as they should (not a real issue, but still annoying). They have the exact structure of the templates which I have designed, but for some reason they will not extract as specified. Is there some function / method I should use? Could the formula of each cell be the cause of this? Any suggestions?

thanks again

mikerickson
02-07-2008, 07:26 AM
myRange.Value = myRange.Value
When applied to an entire sheet, its best to restrict the range.
With ThisWorkbook.Sheets("Sheet1").UsedRange
.Value = .Value
End With

The second problem depends heavily on your exact code, but using Select can cause unexpected errors and is usually not needed.