PDA

View Full Version : Solved: Copy one cell to another using VBA



canselmi
03-10-2008, 09:23 AM
This sounds simple but all I want to do is once I enter a value in M3 and tab to next cell for that value to be copied to cell M5 using VBA. The issue is once I move past M3 or I think it's "LostFocus" I want it to copy. Thanks in advance.

I'm lost. My simple code so far is:

Range("M3:N3").Select
Selection.Copy
Range("M5:N5").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Bob Phillips
03-10-2008, 09:28 AM
You only need



Range("M3:N3").Copy Range("M5:N5")


but that should work. Is it part of a Worksheet_Change event? If so, shouldn't you use Target?

canselmi
03-10-2008, 09:32 AM
Yes, it's part of a Worksheet_Change event. The problem is that If I type a value in cell M3 and move past M3 I need it copy the value to M5, but only if there is a value in M3.

Bob Phillips
03-10-2008, 09:40 AM
Show us the code mate.

canselmi
03-10-2008, 10:02 AM
xld, I'm very new to VBA, but learning. The above code is all of the code I have and it is in a module. I have 7 worksheets and need the copy and paste to work in all 7.

Bob Phillips
03-10-2008, 10:26 AM
Try this event then



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "M3" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(, 2).Copy .Offset(2, 0)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

canselmi
03-10-2008, 12:16 PM
YOU ARE THE BOMB!!!!! It's knowing where to put it. I have to put it into each sheet not Module1. Thank You...

Aussiebear
03-10-2008, 03:17 PM
LOL.... you've been called many things Bob, but a "bomb"?

lucas
03-10-2008, 03:23 PM
LOL.... you've been called many things Bob, but a "bomb"?


Ted I think it's "THE Bomb"
I understand it to be a complement of the highest order. I agree when applied to Bob....:thumb

Kids these days.......

Bob Phillips
03-10-2008, 04:17 PM
LOL.... you've been called many things Bob, but a "bomb"?

Oh! I just read it as ... you are a bum ... Shows what you are expecting.

Aussiebear
03-11-2008, 03:43 AM
Good lord Bob, you've got your glasses on upside down again...