VBA cut and paste
 

I'm using Excel 2003 and want to move data from a cell in column C to column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

I think you will have to use Target rather than Selection and ActiveCell...
since this is in the Change event, I believe the ActiveCell has moved on to
another cell (in accordance with your Option setting) when you keyed in the
Enter key.

--
Rick (MVP - Excel)


"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

I always like the KISS idea

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Or Target.Row < 2 Then Exit Sub
If Left(Target, 1) = "6" Then Target.Cut Target.Offset(, 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

I tested your code and it worked

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

Try it this way

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If Target.Column = 3 And Target.Row 1 Then 'ignore row 1
Application.EnableEvents = False
Dim LResult As String
If Left(Target.Value, 1) = "6" Then
Target.Offset(, 1).Value = Target.Value
Target.Value = ""
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

"JSnow" wrote:

I'm using Excel 2003 and want to move data from a cell in column C to column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

It didn't work for me.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

I tested your code and it worked

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

Small modification

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LResult As String
Dim HoldRow As Integer
Application.EnableEvents = False
On Error GoTo Whoops
HoldRow = Target.Row
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Cells(HoldRow, 4) = LResult
Cells(HoldRow, 3).ClearContents
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

BRILLIANT! Thanks so much.

"Mike H" wrote:

Try it this way

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If Target.Column = 3 And Target.Row 1 Then 'ignore row 1
Application.EnableEvents = False
Dim LResult As String
If Left(Target.Value, 1) = "6" Then
Target.Offset(, 1).Value = Target.Value
Target.Value = ""
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

"JSnow" wrote:

I'm using Excel 2003 and want to move data from a cell in column C to column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

Don,

Agree about KISS but your code will execute twice because of the worksheet
change event when the cut value is pasted in, would you really recommend that?

Mike

"Don Guillett" wrote:

I always like the KISS idea

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Or Target.Row < 2 Then Exit Sub
If Left(Target, 1) = "6" Then Target.Cut Target.Offset(, 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

The OP's code will only work if you don't move cells on enter, see Rick's
comments on target

Mike

"Don Guillett" wrote:


I tested your code and it worked

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

One possible problem for you with Mike's solution... it doesn't duplicate
the Cut/Paste operation as Excel performs it. If the Target cell had
formatting, Mike's code leaves that formatting in place whereas Cut would
remove it. Also, Paste copies the formatting from the Cut cell over into the
new location whereas Mike's code doesn't.

The most direct route to do what you want is based on the code that Don
posted...

......
If Left(Target.Value, 1) = "6" Then
Target.Cut Target.Offset(, 1)
End If
......

but if you want to do it in two steps as Mike proposed, these steps would do
what Cut/Paste does...

......
If LResult = "6" Then
Target.Copy Target.Offset(0, 1)
Target.Clear
End If
......

--
Rick (MVP - Excel)


"JSnow" wrote in message
...
BRILLIANT! Thanks so much.

"Mike H" wrote:

Try it this way

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If Target.Column = 3 And Target.Row 1 Then 'ignore row 1
Application.EnableEvents = False
Dim LResult As String
If Left(Target.Value, 1) = "6" Then
Target.Offset(, 1).Value = Target.Value
Target.Value = ""
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

"JSnow" wrote:

I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

Rick,

Good point, I assumed (never safe to do) that the OP used cut & paste
because he didn't know any other methods.

Mike

"Rick Rothstein" wrote:

One possible problem for you with Mike's solution... it doesn't duplicate
the Cut/Paste operation as Excel performs it. If the Target cell had
formatting, Mike's code leaves that formatting in place whereas Cut would
remove it. Also, Paste copies the formatting from the Cut cell over into the
new location whereas Mike's code doesn't.

The most direct route to do what you want is based on the code that Don
posted...

......
If Left(Target.Value, 1) = "6" Then
Target.Cut Target.Offset(, 1)
End If
......

but if you want to do it in two steps as Mike proposed, these steps would do
what Cut/Paste does...

......
If LResult = "6" Then
Target.Copy Target.Offset(0, 1)
Target.Clear
End If
......

--
Rick (MVP - Excel)


"JSnow" wrote in message
...
BRILLIANT! Thanks so much.

"Mike H" wrote:

Try it this way

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If Target.Column = 3 And Target.Row 1 Then 'ignore row 1
Application.EnableEvents = False
Dim LResult As String
If Left(Target.Value, 1) = "6" Then
Target.Offset(, 1).Value = Target.Value
Target.Value = ""
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

"JSnow" wrote:

I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

Notice I said "one **possible** problem". If the OP doesn't have any
formatting on the Target cell, then your code will work fine for him; I just
thought it important to point out the differences in how your code works and
how the OP was trying to do it... just in case it mattered later on,
perhaps.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Rick,

Good point, I assumed (never safe to do) that the OP used cut & paste
because he didn't know any other methods.

Mike

"Rick Rothstein" wrote:

One possible problem for you with Mike's solution... it doesn't duplicate
the Cut/Paste operation as Excel performs it. If the Target cell had
formatting, Mike's code leaves that formatting in place whereas Cut would
remove it. Also, Paste copies the formatting from the Cut cell over into
the
new location whereas Mike's code doesn't.

The most direct route to do what you want is based on the code that Don
posted...

......
If Left(Target.Value, 1) = "6" Then
Target.Cut Target.Offset(, 1)
End If
......

but if you want to do it in two steps as Mike proposed, these steps would
do
what Cut/Paste does...

......
If LResult = "6" Then
Target.Copy Target.Offset(0, 1)
Target.Clear
End If
......

--
Rick (MVP - Excel)


"JSnow" wrote in message
...
BRILLIANT! Thanks so much.

"Mike H" wrote:

Try it this way

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If Target.Column = 3 And Target.Row 1 Then 'ignore row 1
Application.EnableEvents = False
Dim LResult As String
If Left(Target.Value, 1) = "6" Then
Target.Offset(, 1).Value = Target.Value
Target.Value = ""
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

"JSnow" wrote:

I'm using Excel 2003 and want to move data from a cell in column C
to
column
D (same row) if the data starts with a six. Here's my code thus
far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

Rick, I just retested it and it worked as he had it written.
xl2003 vista HP


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
It didn't work for me.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

I tested your code and it worked

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

Just re-tested and didn't notice...

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike H" wrote in message
...
Don,

Agree about KISS but your code will execute twice because of the worksheet
change event when the cut value is pasted in, would you really recommend
that?

Mike

"Don Guillett" wrote:

I always like the KISS idea

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Or Target.Row < 2 Then Exit Sub
If Left(Target, 1) = "6" Then Target.Cut Target.Offset(, 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

I am also using XL2003 on Vista (Ultimate edition though, but that shouldn't
matter). Do you have the "Move selection after Enter" checkbox unchecked on
the Tools/Options/Edit(tab) dialog? That would be the only way I can see the
OP's code working for you.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Rick, I just retested it and it worked as he had it written.
xl2003 vista HP


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
It didn't work for me.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

I tested your code and it worked

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

Rick, I ALWAYS have that box checked.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I am also using XL2003 on Vista (Ultimate edition though, but that
shouldn't matter). Do you have the "Move selection after Enter" checkbox
unchecked on the Tools/Options/Edit(tab) dialog? That would be the only way
I can see the OP's code working for you.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Rick, I just retested it and it worked as he had it written.
xl2003 vista HP


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
It didn't work for me.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

I tested your code and it worked

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

UN

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Rick, I ALWAYS have that box checked.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I am also using XL2003 on Vista (Ultimate edition though, but that
shouldn't matter). Do you have the "Move selection after Enter" checkbox
unchecked on the Tools/Options/Edit(tab) dialog? That would be the only
way I can see the OP's code working for you.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Rick, I just retested it and it worked as he had it written.
xl2003 vista HP


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
It didn't work for me.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

I tested your code and it worked

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

Don

In a implementation as trivial as this it's so fast you wouldn't notice but
try this and see how may times you have to tap F5 to exit the code

Private Sub Worksheet_Change(ByVal Target As Range)
Stop
If Target.Column < 3 Or Target.Row < 2 Then Exit Sub
If Left(Target, 1) = "6" Then Target.Cut Target.Offset(, 1)
Stop
End Sub

Mike
"Don Guillett" wrote:

Just re-tested and didn't notice...

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike H" wrote in message
...
Don,

Agree about KISS but your code will execute twice because of the worksheet
change event when the cut value is pasted in, would you really recommend
that?

Mike

"Don Guillett" wrote:

I always like the KISS idea

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Or Target.Row < 2 Then Exit Sub
If Left(Target, 1) = "6" Then Target.Cut Target.Offset(, 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

I'm guessing your two messages together are saying you do not have that
option checked. That would be why the OP's code works for you, but not for
me (I have the option checked). For you, the ActiveCell remains as the
Target cell after editing is complete... for me (and a lot of others out
there I would guess), the ActiveCell moves with the completion of editing
meaning that the ActiveCell is not the same as the Change event's Target
cell.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

UN

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Rick, I ALWAYS have that box checked.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I am also using XL2003 on Vista (Ultimate edition though, but that
shouldn't matter). Do you have the "Move selection after Enter" checkbox
unchecked on the Tools/Options/Edit(tab) dialog? That would be the only
way I can see the OP's code working for you.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Rick, I just retested it and it worked as he had it written.
xl2003 vista HP


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
It didn't work for me.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

I tested your code and it worked

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C
to column
D (same row) if the data starts with a six. Here's my code thus
far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

VBA cut and paste
 

You're right. That's the way I roll.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I'm guessing your two messages together are saying you do not have that
option checked. That would be why the OP's code works for you, but not for
me (I have the option checked). For you, the ActiveCell remains as the
Target cell after editing is complete... for me (and a lot of others out
there I would guess), the ActiveCell moves with the completion of editing
meaning that the ActiveCell is not the same as the Change event's Target
cell.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

UN

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Rick, I ALWAYS have that box checked.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I am also using XL2003 on Vista (Ultimate edition though, but that
shouldn't matter). Do you have the "Move selection after Enter" checkbox
unchecked on the Tools/Options/Edit(tab) dialog? That would be the only
way I can see the OP's code working for you.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Rick, I just retested it and it worked as he had it written.
xl2003 vista HP


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in
message ...
It didn't work for me.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

I tested your code and it worked

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
I'm using Excel 2003 and want to move data from a cell in column C
to column
D (same row) if the data starts with a six. Here's my code thus
far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.