Copy Paste values between sheets - values incorrect

[db41987]

Please Login or Register  to view this content.

All sheets located in the same workbook.
I am copying a model number (Sheet "Models") into a bill of material spreadsheet (Sheet "Bill"), refreshing and taking the cost of the model (H2 cell) and copying it to another spreadsheet (Sheet "Cost Pricer") to develop a price list.

The above formula plugs product model costs into two cells... C6 and C7.

When I run the macro with only one cell (i.e. half the macro or eliminating the macro to plug a value into C7) everything works out fine. If I run the macro for additional cells (i.e. C7 or C8 or C9 or C10 etc)...the last cell will copy the correct values...but previous cells the values are incorrect and I am uncertain as to why this is happening...example:

SHOULD BE:
C6 $57.77
C7 $58.99

With one cell macro only I get
C6 $57.77 (CORRECT VALUE)

With more than one cell I get
C6 $52.88 (WRONG VALUE)
C7 $58.99 (CORRECT VALUE)

Why does the addition of another cell to the macro cause inaccurate values in all cells except the very last cell in the macro?

Thank you in advance

[Leith Ross]

Hello db41987,

Welcome to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code using the # icon

1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

To manually wrap your code, use these Bulletin Board Code Tags

[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.

To learn more about BB codes used in this forum, just click on the link below...

Bulletin Board Codes Tags

[shg]

Selecting is unnecessary, slows your code, and makes it hard to follow. The first half can be simplified to something like this:

Please Login or Register  to view this content.

[db41987]

Please Login or Register  to view this content.

Thank you for the code optimization snippet...I've incorporated this.

Unfortunately, my end result is the same. The values posted are only accurate for the LAST cell in the sub...the previous cells have incorrect values and I'm not sure why.

[shg]

Have you stepped though the code and watched the values as they are copied and the cells as they calculate?

[db41987]

Have you stepped though the code and watched the values as they are copied and the cells as they calculate?

That's a good question.

When I manually enter the model number into the Bill of Material spreadsheet it computes the cost correctly.

When I run the script and watch the Bill of Material compute the cost...the costs shown are not correct for all of the models except the very last one...that one is correct.

If I manually enter a model number, calculate and THEN run the script...that particular cell is computed properly...all others are in error except for the last one.

Then...if I run the script immediately afterwards...it overwrites the correct cost with an incorrect cost...again...all cells wrong except for the very last one.

[db41987]

Okay I just found that on cells where a value of $0 is posted...instead of posting $0 it is posting the value in the cell underneath it...

H2 is the proper cell...if it is $0 it is taking the value of H3 and posting it

Not sure why?