code for automatic: copy-paste ?

[Pericic]

Is it possible to write a code for the following:

In a table of 3 x 3 (for example) with data in a1:c3-
Is it possible to copy values of a1:c3 to e1:g3 AUTOMATICALLY when the last value in c3 is entered??????

Thanks

 

[erik.van.geit]

Hello, Pericic,

try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CountA(Range("A1:C3")) = 9 Then Range("E1:G3").Value = Range("A1:C3").Value
End Sub

setting it up with some variables will make ot more easy to edit

without code
select E1:G3
type formula: =IF(COUNTA($A$1:$C$3)=9,A1,"")
Control-Enter

kind regards,
Erik

 

[Pericic]

Erik, thanks for the great input.....it works great.....but

What if I wanted to copy only the values in the table??? (assuming the values change at certain intervals and I want to keep the original values)
What do I need to add to the code or formula...

With your solution the copied cells change as the values in the original change....I'd like to prevent them from changing once they are copied!!!

Once again, your effort is much appreciated..

 

[Pericic]

PS

I guess what I'm asking is something to this effect:

IF(condition met, copy-paste-values only,"")

Thanks again

 

[erik.van.geit]

try

If Application.CountA(Range("A1:C3")) = 9 And Application.CountA(Range("E1:G3")) = 0 Then Range("E1:G3").Value = Range("A1:C3").Value

since we're using the same rangereference twice i would put the ranges in a variable

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG1 As Range
Dim RNG2 As Range

Set RNG1 = Range("A1:C3")
Set RNG2 = Range("E1:G3")
If Application.CountA(RNG1) = 9 And Application.CountA(RNG2) = 0 Then RNG2.Value = RNG1.Value
End Sub

there is still a minor "problem"
the code triggers itself a second time when writing to RNG2, since this is a sheetchange
in this case we don't care in other circumstances we will have to prevent this

kind regards,
Erik

 

[pfarmer]

Erik, thanks for the great input.....it works great.....but

What if I wanted to copy only the values in the table??? (assuming the values change at certain intervals and I want to keep the original values)
What do I need to add to the code or formula...

With your solution the copied cells change as the values in the original change....I'd like to prevent them from changing once they are copied!!!

Once again, your effort is much appreciated..

If I understand you correctly:

Assuming that the routine places a value in cell e3 where none existed before, you would check to see if e3 contained a value.

Something like:

if worksheet("yoursheetname").range("e3") <> "" then exit sub

Once you ran the routine, e3 should contain a value and the routine should exit without processing the rest of the routine.

In my case I cause it to actually exit to a sub that provides a message to a text box telling you that you have previously copied that item.

Perry

 

[Pericic]

Thanks guys, I'm losing both of you.....
Obviously, I'm not gifted as the two of you in excel....

In the simplest terms this is what I'd like to achieve:

If one has values in cells based on certain formulas, one can copy - paste - values only elsewhere......

All I need is for this to be done automatically, somehow...i,e, copy and paste values only (not formulas) somewhere else....

Thanks for putting up with us "rookies" of excel
I greatly appreciate your efforts...

 

[erik.van.geit]

don't panic
but be specific and clear
continue using cellreferences to talk about ...

are these statements correct ?
1. a1:c3 have formulas which can result in empty
example: A1: =IF(A10="","",A10)
2. e1:g3 is empty
3. when a1:c3 have ALL a value copy those values to e1:g3
4. task completed

if this is not correct provide another simple scheme please

persisting will get you were you want :wink:
Erik

 

[Pericic]

Erik,
a1:c3 will all eventually have a value (based on a formula)...
Once they all have a value, I need to copy those values elsewhere beforethey change (and they will change as the formulas use new data)

My answer to your 4 statements is EXACTLY...

Thank you so much and happy thanksgiving

 

[erik.van.geit]

this would do the job

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG1 As Range
Dim RNG2 As Range

Set RNG1 = Range("A1:C3")
Set RNG2 = Range("E1:G3")
If Application.CountIf(RNG1, "") = 0 And Application.CountIf(RNG2, "") = 9 Then _
RNG2.Value = RNG1.Value
End Sub

if the RNG1 gets his changes from precedents on the same sheet