VBA code required to save formula derived data as data.

[Malcolm McMaster]

Hi All,

Bear with me here, I have an excel sheet that has formulas to help populate cells during the data entry process. The issue I have is that because the formulas are active & therefore if there is a change to the source data being referenced by the formula, the history within the sheet will become corrupted.

For example, if there is a unique id used for a supplier that is used to populate their name & other relevant data, should their ID change, the entire workbook storing the data can no longer retrieve the suppliers information. What I would like to do is, once the data entry session has been completed, all data entered should be saved as data and no longer interacting with the formula, something like copy the data & paste as special using the data only symbol. I think what I need is a code to copy all "non-blank cells and paste as data, leaving the rest of the workbook still with the formulas ready to react to the next data entry session. Any assistance would be greatly appreciated.

[#2]

@Malcolm McMaster 

The problem by copying the data is that your formulas will be lost, and will be necessary write all the formulas again when interacting with another amount of data.

I would suggest you to protect your data, I mean, the range where is sensitive any change.

[#3]

Thanks for the reply. I was hoping the copy & paste process could be selectively applied only to
to the cell range containing data. The rest of the workbook would still contain the formulas for future data entry assistance.