Copy and Paste Special Values Macro ignoring cells with specific condition

Hi,

I am new to the forum and am looking for help creating a macro. The macro needs to perform the following task.

Copy and Paste Special Values any cells in the range O4 and AC5000 in sheet named At Terminal that do not contain the text TBD. The cells are only being pasted over the top of the existing cells to remove a positive result from a formula (TBD is a text response as a false response, the underlying formula should remain as a true result may follow at a later time).

I have tried modifying existing macro's I was able to find but had no success.

Looking forward to any help!

Thank you,

Cassandra

Hi chendriks,
Welcome to the forum. Could you post a sample workbook of your data and your desired result to better describe your problem

Please find the attached file:

In the delivery advice docket sheet the quantity delivered will be entered. The quantity delivered is pulled using an IF/AND formula to the relevant field in the At Terminal tab (cell ranges O4:AC100 (will be 5000 but have deleted some rows to make file smaller for upload). If the field contains TBD it is not listed on this Delivery Advice and as such I want the formula to remain in place for the next manifest that is entered.

The paste special values is required to override the positive result as the manifest ID being changed would replace this information each time it was changed.

I'm either looking for a macro that will paste special values in fields that either do not contain TBD or a macro that will enter the formula back into all cells that are not blank after paste special of all cells.

Hope this helps,

Cassandra

Sorry that should read ***that are blank not that are not blank.

Cassandra

I don't know if I could understand you right but this code will replace the formula with its value if its not blank and not TBD; it should be place in Sheet1 code module

Please Login or Register  to view this content.

That does exactly what I wanted it to do! Thank you very much, I really appreciate it.

Cassandra