copy paste problem if target cell contains formula

**masterx** · 11-22-2010, 05:17 AM

Hi,

I have a big dataset in a particular sheet. Each month the outcome of the figures in the cells CJ87:CV1000 needs to be copied and pasted as hard values in cells L87:w1000. (This in order to start with updated data for the new month). Columnheader January is represents by column L (old figures) and CJ (new figures to be copied into L) and February is M and CK and so on.

All the values in range CJ87:cv1000 are calculated by formulas, but multiple rows in range L87:w1000 are subtotals(calculated) and other cells hard values. The formulas in this range needs to be kept untouched.

I have 2 ways in mind to let this work.

1-

each cell with a value in range CJ87:CV1000 needs to be copied, then offset (0, -77) and pasted as value. But if the target cell contains a formula than the paste needs to be aborted and the macro must find the next cell in the range.

or 2-

The dataset is completely protected, but not the range CJ87:CV1000.
The macro search for not protected cells. Finds the first unprotected cell, then unprotects the sheet and offsets -77 and pastes the value. Than it protects the sheet again and searches for the next unprotected cell and repeats the whole process as above.

Thankx for the advise.

**teylyn** · 11-22-2010, 06:06 AM

You mention a macro, yet you posted the question in the Worksheet Functions forum. I've moved the question to the Programming forum. Please take care to post in the appropriate forum.

cheers,

**masterx** · 11-22-2010, 09:23 AM

This code is in a way working, but it doesn't copy cells wich contains formulas to the target cells as values. It must copy all the cells as hard values to target, but must abort the action if the target cell contains a formula. Help wanted.

Please Login or Register  to view this content.

**teylyn** · 11-23-2010, 06:20 AM

Masterx,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I added the code tags for you, but this time only. Please read the forum rules and use code tags when posting code.