code for automatic: copy-paste ?

Pericic

Board Regular
Joined
Nov 11, 2005
Messages
204
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, Pericic,

try this
Code:
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

Board Regular
Joined
Nov 11, 2005
Messages
204
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

Board Regular
Joined
Nov 11, 2005
Messages
204
PS

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

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

Thanks again
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

try
Code:
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

Code:
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

Well-known Member
Joined
Jul 6, 2005
Messages
550
Pericic said:
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

Board Regular
Joined
Nov 11, 2005
Messages
204

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Nov 11, 2005
Messages
204
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
this would do the job
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,070
Members
412,762
Latest member
sienweiw
Top