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]

to watch a change on any sheet
you can use this

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim RNG1 As Range
Dim RNG2 As Range

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

 

[Pericic]

ERIK,
I don't know if anyone has ever told you this before but....

YOU ARE A GENIUS!!!!!!!!!!!!!

It does work and it is exactly what I needed...(the reason I had problems was that I had your initial formula in e1:g3)

You have my gratitude, respect, and a huge THANK YOU

 

[Pericic]

ERIK,
One more question if I may....

If I were to insert, lets say a few rows would the code adjust the ranges???

If not, how does the code need to be adjusted.....

and.....

 

[Pericic]

...and

perhaps a simpler version of the problem.....
If I had 2 columns: A and B...
How can I copy the first value placed in a cell in column A to the corresponding cell in column B (A10 to B10 for example) and keep the value in column B permanent....in other words, even if I subsequently delete the original value in A10 - make the value in B10 still be there

Thanks for your phenomenal insights

 

[erik.van.geit]

ERIK,
If I were to insert, lets say a few rows would the code adjust the ranges???

If not, how does the code need to be adjusted....

the code is what we call "hardcoded"
depending of your needs we have to elaborate a strategy
this is one of them, which I think would be the most appropriate for you (but it's nothing much more than a guess)
define a named range
a1:c3 = "data"
your code would look like

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim RNG1 As Range
Dim RNG2 As Range

Set RNG1 = Sheets(1).Range("data")
Set RNG2 = Sheets(1).Range("E1").Resize(RNG1.Rows.Count, RNG1.Columns.Count)

If Application.CountIf(RNG1, "") = 0 And Application.CountIf(RNG2, "") = RNG2.Cells.Count Then _
RNG2.Value = RNG1.Value
End Sub

note that this code is still meant to be used for changes triggered by formulas (but can be applied for simple value changes too)

How can I copy the first value placed in a cell in column A to the corresponding cell in column B (A10 to B10 for example) and keep the value in column B permanent....in other words, even if I subsequently delete the original value in A10 - make the value in B10 still be there

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
'use one of both syntaxis
'If Target.Offset(0, 1) = "" Then Target.Offset(0, 1) = Target
If Cells(Target.Row, 2) = "" Then Cells(Target.Row, 2) = Target
End Sub

experimenting will learn you a lot

best regards,
Erik

 

[Pericic]

Erik,
You are really a wonderful person...and I cannot tell you how much you have helped me out....

Your solutions are awesome....but I have 1 question...
Why is it that when I copy and paste more than 1 value at once into column A - it doesn't get into column B? Only when I do it one at a time does it work??

If you don't have time, it's ok...this is me playing around and experimenting now....(I actually ordered the power bundle from the site to get educated more)

PS
I Love Jesus, too...
and Belgium (as all Croatians do)

THANK YOU

 

[erik.van.geit]

thank you for the compliments

try this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub

For Each cell In Intersect(Target, Columns(1))
If cell.Offset(0, 1) = "" Then cell.Offset(0, 1) = cell
Next cell

End Sub

see what this will do in different situations

enjoy !
Erik

EDIT: changed code which was not OK in all circumstances

 

[Pericic]

EXACTLY what I was looking for.....

My 2 year search for this answer has come to an end......

Thank you ERIK.VAN.GEIT aka "THE GOD"

With deepest respect
Romeo Pericic, MD
New York

 

[Pericic]

Copy Paste Answer.xls
	A	B	C	D
1			1	1
2			2	2
3			3	3
4	1	1	1
5	2	3	6
Sheet1

Erik,
I'm so sorry to bother you again, but I still have a problem....my 2 year search continues..although I think you will find the solution....

This is the current problem...
Your last code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column<> 3 Then Exit Sub
For Each cell In Target
If cell.Offset(0, 1) = "" Then cell.Offset(0, 1) = cell
Next cell
End Sub

works fine with everything except when there is a formula in the cell.....please see example....

When I enter values in c1, c2, or c3 (no formulas in these cells) the values get copied into d1, d2, and d3 without problems...

Cells c4 and c5 have formulas: and once the values are entered into a4 and b4 and a5 and b5 the results show up in c4and c5....but are not copied into d4 and d5!!!!!

I believe this is because d4 and d5 see the cells c4 and c5 as containing some type of values already.......
Perhaps trying to set up - copy only if there is a text or value >0 in the cell??

Thanks

 

[erik.van.geit]

YES!
we are again looking for a solution which looks to every change in the workbook which changes the cells
remember this kinda code
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

I'm just to tired now to continue today
anyway I would suggest you experiment putting the last code into the Workbook_SheetChange event
this would be your homework

some detail-study of the code and some trial and error will get you really further
(feel free to come back if you don't get out of it)

best regards,
Erik