automatically copy and paste values

**robbi100** · 12-24-2008, 05:38 AM

Hi there,

I need to automatically copy cell IR18 value to cell JT18. I've been searching the forum for some code and I came up with something that only partially works for me:

Please Login or Register  to view this content.

This code works fine if I manually fill IR18, the problem is that IR18 contains an IF formula normally set to "", when IS18 is populated (which is populated when the array "calendar!I162:calendar!J171 is manually filled) IR18 returns a number but I get nothing on JT18, so I changed the above code to:

Please Login or Register  to view this content.

I don't have any code error but still it doesn't work. Any suggestions? Or a totally new code?

Thank you in advance and merry xmas you all.

**Andy Pope** · 12-24-2008, 05:52 AM

You can not create your own events in that way.

Worksheet_Change2 would be a module private the sheet but nothing would cause it to run unless explicitly called.

Try something like this which checks all cells that are involved.

Please Login or Register  to view this content.

Any reason why JT18 can not reference IR18 via a formula?

**robbi100** · 12-24-2008, 06:36 AM

What a quick response! I'm amazed.

Any reason why JT18 can not reference IR18 via a formula?

Column IR was meant to be the x values of a dynamic chart, the IF formula in it prevents numbers to be displayed still the chart recognize the "empty cells" containing formulas as not empty becoming very much static, that's why I need to copy IR18:IR39 array to JT18:JT39 leaving "future cells" completely empty, I tried to copy IR18:IR39 and paste only values on JT18:JT39 so that I;ve officially empty cells (nothing appear on the formula bar) but the chart still recognize them as not empty, I know because when I clear the contents of that "empty cells" the chart works, i thought that a macro would be the only way.

about your code I don't have any errors but it doesn't work, maybe because the macro is in sheet2 while I162:J171 array is in sheet1 called "calendar", I tried to refer the array in your code to sheet1 but I'm really new to vba and couldn't get anything good.

thank you very much for your time and efforts

**Andy Pope** · 12-24-2008, 06:40 AM

You don't show your formula but a guess would be

=IF( <test> , <Value> . "" )

in which case the chart will treat the "" as text and text values are plotted as zero.

If you alter your formula to

=IF( <test> , <Value> . NA() )

then in a line chart the data marker will not be displayed. The line itself will not be broken but it will only be plotted between valid data points.

**Andy Pope** · 12-24-2008, 06:43 AM

If the information in I162:J171 is on another sheet you will need to add code to that sheets event.

Please Login or Register  to view this content.

change sheet1 to whatever is appropriate.

**robbi100** · 12-24-2008, 07:14 AM

My formula is

=IF(IS18="","",IR17+1)

I changed to

=IF(IS18="",NA(),IR17+1)

so I get #N/A instead of "" cells, I then edit the name range of my x values dynamic chart from JT column to IR column but the x assis shows all the points and not just the active ones.

Btw the code works great and I can manage with it, don't know if you've any further suggestion regarding the formula but it's Christmas Eve and I certainly don't want to keep you busy, yours was the quickest response I ever had, I'm really surprised and I greatly appreciated.

Have a good time, thank you so much.

**Andy Pope** · 12-24-2008, 07:22 AM

really need to see your formula for the named range.
I would guess it is not taking into account that the NA's need to be excluded.

Can you post workbook?

**robbi100** · 12-24-2008, 07:30 AM

Thank you very much,

I need to prepare a simplified workbook as the original is very heavy, right now I'm going to dinner so I'll to give you a break. As it is ready I will post it.

thank you again

**robbi100** · 12-24-2008, 02:17 PM

here's the workbook, I cut everything unnecessary still is big, I wrote a brief explanation on sheet1, hope it's understandable.

I also hope to get it right with formulas.

Again, when I fill the array I162:J171 in sheet1 I'd like to automatically copy the array HN164:IN173 in sheet2 (that contains formulas) and paste values on the very same array so that data will not be modified by further inputs.

Well, if you feel I'm asking too much don't hesitate to tell me, I will post these last requests in a new thread, of course take your time, it's holiday now.

Thank you and Merry Christmas