Copy/Paste Values

[Gamma48]

I am setting up a pricing list that is regularly edited by the user. At the top of each category, I have a running total cost of the included items in the category. The item cost is entered by the user.

I have a formula in a helper column that calculates the total cost of each category and then referenced back on the main list. I've been trying to build a macro that would trigger when the value in Column D changes and copies (or updates if already present) the cell next to it in Column C.

I've been trying to work with a couple different ideas:
Setting KeyCell as range and triggering macro upon cell value change
and
An If statement with nonblank cells (if A5 and B5 <> "", then value of C5 = D5)

Some new insight would be amazing and more than welcomed. I've attached a simple sheet.

Any questions, please ask. Thanks for the help!

 

[Luke M]

Do you really want/need to have the macro, or would a formula work?
Put this formula in C5, then copy it to any other row where you want a summation. Confirm formula as an array using Ctrl+Shift+Enter, not just Enter.
=SUM(INDEX($C:$C,ROW()+1):INDEX($C:$C,MIN(IF(ISBLANK($C6:$C$1000),ROW($C6:$C$1000)))))

 

[Gamma48]

Thanks Luke. Unfortunately I don't think a formula would work. Each price listing would very in length from one to another. If they needed to add within one category, the hardcoded formula could potentially be out of place. Or worse, if they accidentally deleted the formula. I know to protect the cells from deletion, but again would a shot in the dark at to which cells to protect so the others can still be edited.

I have found a possible error in my ways. I was working with Worksheet_Change when I think I should have been using Worksheet_Calculate. I will report back....

 

[Luke M]

The formula I gave you is flexible enough to handle different sized ranges, letting the user add/delete rows below the summation as necessary. But I can understand your point if the users are going to delete the formulas.

On the other hand, I might suggest a re-structuring of your data. Rather than having the user scroll input data on same sheet that you're doing calculations, separate them. If your data sheet had 4 columns:
Location | Type | Name | Cost

you could then easily build a PivotTable with your desired layout, showing total cost.
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm#Layout

 

[Gamma48]

Thanks for giving me another way to look at the issue. Going to read through it now...

 

[Gamma48]

Forgive me Luke, but can you (or anyone else that is willing to) take a look over the updated sheet. I'm on another train of thought with the code, realizing it probably wouldn't be able to be modified for my purposes. But I figured it wouldn't hurt to post. The pivot table is still certainly an option...trust me, but I can't keep from thinking that it should be possible with a macro.

It is static right now but funtional in just the first section in the pricing guide. Is there a way to modify the code to work over a whole range instead the one specific cell? Also I took the helper cell out. Thus the updated file attached.

Found on the interwebs and modified to suit:

Private Sub Worksheet_Calculate()
Static OldVal
If Range("D5").Value <> OldVal Then
    OldVal = Range("D5").Value
    Sheets("Sheet1").Range("C5").Value = Sheets("Sheet1").Range("D5").Value
End If
End Sub

Seriously, thanks for the time.

 

[NARAYANK991]

Hi ,

See if this is helpful.

Narayan

 

[Gamma48]

Narayan, first off let me apologize for being away a couple days. Thank you so much for your time. That is slick code. It is something I am going to be saving and studying. Conceptually it makes perfect sense; now the key for me is to be able to apply these concepts to future needs and create it on my own. A number of my attempts involved the worksheet_change, application.intersect and target.offset, but I was messing with an If/Then instead of a Do loop.

I am going to incorporate it into the master file and mess with it over the next day or so to see if I can get it to crash (but I doubt it will). I'll certainly report back.

Thank you again!