If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 -- jkrist46 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Trying to email a Snapshot File via Macro. ERROR: "The formats . | titlepusher | General Discussion | 5 | November 10th, 2005 03:53 AM |
Question about reducing number of tables in a database | tlyczko | Database Design | 0 | October 27th, 2005 04:15 PM |
How to update these figures? | Yorkie118 | Running & Setting Up Queries | 11 | October 22nd, 2005 05:56 PM |
Search my question lost in the long list | Shrikant | General Discussion | 3 | August 26th, 2005 09:32 AM |
How to gray-out a question dialog box | S_Kaplan | General Discussion | 3 | October 26th, 2004 12:11 AM |