Target Question

[jkrist46]

Below is the Macro I have. Instead of doing a copy of entire row I wan
just certain cells in that row (A-H). What would be the best way t
accomplish this.

Private Sub Worksheet_Change(ByVal Target As Range)

****************************************************************************************
'

****************************************************************************************
' monitors any change in the worksheet. If it is confined to a singl
cell that resides
' in our target column, and has a specific value,
' it will copy the entire row to the next blank row in the targe
tab

****************************************************************************************


Dim varAnswer As String
Dim txtMessage As String

txtMessage = "Copy This!"

If Target.Count 1 Then Exit Sub ' more than one cell?
If Target.Column 24 Then Exit Sub ' is this my target row?

Select Case Target.Value ' if matches a case, do the actio
required
Case "X" 'check for both upper and lower case
'confirmation step
varAnswer = MsgBox("Copy to Test Tab?", vbYesNo
txtMessage)
If varAnswer = vbNo Then
Exit Sub
End If
'cut and paste to target tab
Target.EntireRow.Cop
Worksheets("Test").Range("A65536").End(xlUp).Offset(1, 0)
Case "x" 'check for both upper and lower case
'confirmation step
varAnswer = MsgBox("Copy to Test Tab?", vbYesNo
txtMessage)
If varAnswer = vbNo Then
Exit Sub
End If
'cut and paste cut and paste to target tab
Target.EntireRow.Cop
Worksheets("Test").Range("A65536").End(xlUp).Offset(1, 0)
' future use
Case "Cancelled"
Target.EntireRow.Copy Worksheets("Cancelle
Actions").Range("A65536").End(xlUp).Offset(1, 0)
End Select
End Su

 

[Bob Phillips]

Change

Target.EntireRow.Copy

to

Cells(Target.Row,"A").Resize(1,8).Copy

You should also change

Range("A65536").

to

Range("A" & Rows.Count)

for future expansion

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[David McRitchie]

Also see no reason that a test for lowercase "x" and uppercase "X" are
not the same so just check for uppercase after changing Select to...

Select Case UCase(Target.Value)
case "X"
....
case "CANCELLED"
...
End Select