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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0
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..
 
Upvote 0
PS

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

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

Thanks again
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,383
Messages
6,136,244
Members
450,001
Latest member
KWeekley08

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top