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
ADVERTISEMENT
If Application.CountA(Range("A1:C3")) = 9 And Application.CountA(Range("E1:G3")) = 0 Then Range("E1:G3").Value = Range("A1:C3").Value
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
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..
ADVERTISEMENT
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