Solved Copy row except column 'I' to new sheet when value is true

Microsoft corporation Excel 2013 32/64-b...
December 7, 2016 at 13:26:15
Specs: Windows 7
You can see my initial post (explaining the sheet) Here:

http://www.computing.net/answers/of...

This is my current code:

Dim NextRow As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 12 Then
        
        If Target = "Declined" Then
            
            NextRow = Sheets("Virtual Quotes").Range("A" & Rows.Count).End(xlUp).Row
            
            Target.EntireRow.Copy Destination:=Sheets("Virtual Quotes").Range("A" & NextRow + 1)
        End If
        
        If Target = "Accepted" Then
            
            NextRow = Sheets("Follow Up").Range("A" & Rows.Count).End(xlUp).Row
            
            Target.EntireRow.Copy Destination:=Sheets("Follow Up").Range("A" & NextRow + 1)
        End If
        
    End If
    
End Sub


Now I would like it to do the EXACT same work - except NOT fill out/copy over the data in column "I"

Thank you in advance!!

message edited by deesarah


See More: Copy row except column I to new sheet when value is true

Report •

#1
December 7, 2016 at 15:44:46
Your requirements are not clear. After the row is copied to the new sheet do you want Column I to be blank, leaving everything else in the original columns or should Column I be deleted and the rest of the columns shifted to the left?

Either situation is doable with just a few lines of code, but we need to know the exact requirements.

There is one more thing I would like to mention. When asking for help in a forum such as this, it is best that you include all of your requirements right up front. In this case, the additional requirement is fairly simple to achieve. However, in some cases an entire re-write of the code is required. That means that the initial work becomes wasted time and effort. Please take these comments in the spirit that they are intended.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 7, 2016 at 17:57:31
Hi DerbyDad03,

Thank you for the response. And I can totally appreciate the later request. I realized after I had the sheet working - I overlooked this detail, in which I would now like to add.

Once the row is copied to the new sheet, I would like column "I" to contain the original "Accepted/Declined" drop down menu - However I would like the cell to remain blank, and have the user decide whether to fill it in as accepted/declined.
Data in columns A through H (Edited to add: Now A through K) will copy exactly as-is in the sheet prior.

Does this help?

Edited to add: Column 'I' has been adjusted to now be Column "L" (If you look at the code closely).

Thank you,
Sarah

message edited by deesarah


Report •

#3
December 8, 2016 at 00:34:44
✔ Best Answer
You can try the following, this will copy the entire row, including the validation list, then blank out Column I.

Dim NextRow As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 12 Then
        
        If Target = "Declined" Then
            
            NextRow = Sheets("Virtual Quotes").Range("A" & Rows.Count).End(xlUp).Row
            
            Target.EntireRow.Copy Destination:=Sheets("Virtual Quotes").Range("A" & NextRow + 1)

              Sheets("Virtual Quotes").range("I" & NextRow + 1) = Empty
        End If
        
        If Target = "Accepted" Then
            
            NextRow = Sheets("Follow Up").Range("A" & Rows.Count).End(xlUp).Row
            
            Target.EntireRow.Copy Destination:=Sheets("Follow Up").Range("A" & NextRow + 1)
   
             Sheets("Follow Up").range("I" & NextRow  + 1) = Empty
        End If
        
    End If
    
End Sub

message edited by AlwaysWillingToLearn


Report •

Related Solutions

#4
December 8, 2016 at 07:44:06
Thank you! This worked wonderfully - I was also able to apply it to the other sheets as need be! Lets hope this is the final edit required for this workbook! I appreciate everyones help!

:)


Report •

#5
December 8, 2016 at 23:47:12
No problem glad we could help :)

Report •

Ask Question