Target Question
 

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

Private Sub Worksheet_Change(ByVal Target As Range)
' ************************************************** **************************************
'
' ************************************************** **************************************
' monitors any change in the worksheet. If it is confined to a single 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 target 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 action 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.Copy Worksheets("Test").Range("A65536").End(xlUp).Offse t(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.Copy Worksheets("Test").Range("A65536").End(xlUp).Offse t(1, 0)
' future use
Case "Cancelled"
Target.EntireRow.Copy Worksheets("Cancelled Actions").Range("A65536").End(xlUp).Offset(1, 0)
End Select
End Sub

Target Question
 

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)

"jkrist46" wrote in message
...

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

Private Sub Worksheet_Change(ByVal Target As Range)
'

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

************************************************** **************************
************
' monitors any change in the worksheet. If it is confined to a single
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 target
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 action
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.Copy
Worksheets("Test").Range("A65536").End(xlUp).Offse t(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.Copy
Worksheets("Test").Range("A65536").End(xlUp).Offse t(1, 0)
' future use
Case "Cancelled"
Target.EntireRow.Copy Worksheets("Cancelled
Actions").Range("A65536").End(xlUp).Offset(1, 0)
End Select
End Sub


--
jkrist46

Target Question
 

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Bob Phillips" wrote in message ...
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)

"jkrist46" wrote in message
...

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

Private Sub Worksheet_Change(ByVal Target As Range)
'

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

************************************************** **************************
************
' monitors any change in the worksheet. If it is confined to a single
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 target
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 action
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.Copy
Worksheets("Test").Range("A65536").End(xlUp).Offse t(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.Copy
Worksheets("Test").Range("A65536").End(xlUp).Offse t(1, 0)
' future use
Case "Cancelled"
Target.EntireRow.Copy Worksheets("Cancelled
Actions").Range("A65536").End(xlUp).Offset(1, 0)
End Select
End Sub


--
jkrist46