Copy and paste contents using ColorIndex, Excel 2007

[scm2000]

I have a workbook containing a Plan worksheet and Branch worksheet that are exactly the same. The worksheets are formatted with color to indicate where users can make edits: ColorIndex 19 (yellow) for the editable cells, ColorIndex 2 (white) for formulas that they should not change. The users will make edits on the Plan worksheet then copy it to the Branch worksheet, pasting values in the yellow cells and formulas in the white cells. The user will want to make more changes in the yellow cells on the Branch worksheet, and the formulas must reflect the changes. The selection is range I14:K415, being pasted into I14 on the Branch tab.

The editable cells are not contiguous, so a simple copy/paste values is not feasible without losing the formulas already on the Branch worksheet. A solution was proposed seven years ago at this thread http://www.excelforum.com/excel-prog...lor-index.html, but I can't make this work. I get Compile errors.

Can anyone help me make this work? I have tried playing around with Dim and Set, but each time I think I've addressed one compile error, another one pops up. I'm still a newbie at VBA.

Thanks!

[6StringJazzer]

You may be overthinking this. If the user is typing data into the yellow cells, you can just do a plain vanilla Copy/Paste. If a cell has a value, the value will be pasted. If a cell has a formula, the formula will be pasted.

Please Login or Register  to view this content.

The solution you linked to is needed only if your yellow cells in Plan have formulas, but you want to paste the values to Branch.

[scm2000]

> The solution you linked to is needed only if your yellow cells in Plan have formulas, but you want to paste the values to Branch.

Thanks for the quick response, Jeff. Maybe I wasn't clear enough - I do tend to ramble - but yes: the yellow cells in Plan have formulas, and I want to paste the values into Branch. The yellow cells are not contiguous: for example, range I14:K20 white, range I21:K23 yellow, I24:K24 white, I25:K27 yellow and so on. When I paste into Branch, I want to retain the formulas in white, but paste the values from yellow.
Does that help?

Thanks!

scm2000

[6StringJazzer]

Using the code from the other thread that you said didn't compile, I think you intended for the second range to be the second argument to the Sub call, and not a separate line of code. Try this. You need to make sure that color index 19 matches the yellow you are using in your worksheet.

Please Login or Register  to view this content.

[scm2000]

OK, thanks, I'll try that. I'm still not sure about this section
(sorry, I don't know how to get it into the blue box - as you can tell, I'm new to this forum)

Sub BlueNRose(rFr As Range, rTo As Range)

Dim i As Long, j As Long

rFr.Copy
rTo.PasteSpecial Paste:=xlPasteFormulas


I'll go ahead an try this though and see what happens.

Thanks!
scm2000

[scm2000]

OMG that's brilliant! I love it!

Thank you so much!! :D

Minor hiccup with "PasteSpecial method of Range class failed" until I realized that I had merged cells. After that it worked like a charm.

[6StringJazzer]

You're welcome! Now for some housekeeping:

Please add code tags to your post (to put the code in the "blue box"). Simply put

[CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]


after. Or select all the code then click # on the edit controls.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved.

Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."