MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Paste Formula Problem

Posted by randy on February 27, 2001 6:57 PM

I've got a problem that is driving me crazy. I'm about to give up and do it the hard way. But I'll ask for your help first. Here it is:

I've got a range of cells in a worksheet - some with values, some with formulas. There is 20 other sheets in the book - all identical, except for the values.

Anyhow, I've re-done a number of the formulas in one sheet (very time consuming) and I want to paste the new formulas over the old ones in all the other sheets - but I do not want to change any of the cells with existing data. I only want to change the cells that have formulas.

Problem is, in order to do only one single copy/paste per sheet, I have to select a continuous range of cells. However, this continuous range has also cells with only values in them. Therefore when I paste the range, not only do I get my new formulas pasted (good), I also get the values pasted (bad).

The strange thing is that even if I choose paste/special/formulas, I also wind up pasting over any values! Thereby ruining the data.

In my opinion, paste formulas should read "paste formulas AND values, but not formatting."

What can be done about this?

Thanks a lot

Posted by Mark W. on February 27, 2001 7:10 PM

Randy, I'm sorry to say that you missed a "golden" opportunity to accomplish your objective. If you had grouped all of the worksheets in your workbook before making formula edits the results of those edits would have been propogated to corresponding cells on each worksheet.

Posted by David Hawley on February 27, 2001 7:45 PM

Randy, not too sure I follow, but....

Select your destination column with the formulas that you are going to replace. Push F5>Special-Formulas-OK. now push delete, this will clear all you fomulas. Now copy your column of formulas and values and copy then select the other Column and PasteSpecial-Skip Blanks.

Of course this will only work if both Columns are the same.

DaveOzGrid Business Applications