Solved Copy entire excel row to a new sheet if a value is true?

Microsoft Excel 2010 - complete product...
December 6, 2016 at 13:02:40
Specs: Windows 7
I need a code for the follow scenario: Also if you can kindly help with how to input the code.
Thank you in advance.

My Sheets are Titled:
Sheet 1: Master Clients
Sheet 2: Current Quotes
Sheet 3: Virtual Quotes
Sheet 4: Follow Up

On sheets 2-4, column ' I ' has a drop down with 2 options: Accepted or Declined.

If "Declined" is selected on sheet 2 (Current Quotes) I would like to copy the ENTIRE ROW over to sheet 3 (Virtual Quotes).

Task #2:

If "Accepted' is selected on sheet 3 (Virtual Quotes) I would like to copy and paste the ENTIRE ROW over to sheet 4 (Follow Up).

On all sheets there is a total of 12 columns (letters A through L).

I appreciate your help in advance. Thank you.

message edited by deesarah


See More: Copy entire excel row to a new sheet if a value is true?

Report •

#1
December 7, 2016 at 01:11:49
✔ Best Answer
try this code, you will need to place the code in the 'Current Quotes' module and in the 'Virtual Quotes' module

Copy this in 'Current Quotes'

Dim NextRow As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 9 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
        
    End If
    
End Sub

Copy this to 'Virtual Quotes'

Dim NextRow As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 9 Then
        
        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




Report •

#2
December 7, 2016 at 06:27:30
Hi AlwaysWillingToLearn,

I am receiving an error - and maybe you can help as to why... I will list the steps I follow in sequence.

1. Open the Excel File
2. Left click the "Current Quotes" sheet and select 'view code'
3. Copy and paste the code you've helped with
4. Close the "code dialogue box"
5. Try to fill in the form.
6. When I fill in column 'I' with 'declined' i recieve and error message that reads:

"Run-time error '6':
Overflow

With 3 click options "End" "Debug" "Help"

When I Click Debug It highlights the 5th row that reads:

NextRow = Sheets("Virtual Quotes").Range("A" & Rows.Count).End(xlUp).Row

7. Nothing is copied into the next sheet.

Few things I forgot to initially mention (and I am not sure if it makes a difference) - The data input starts on row 3 (Rows 1 & 2 are Headers).

The Accept/Decline row is in the middle of the data that needs to be copy and pasted into the next sheet. (There are 3 columns following Column 'I' that also need to be copied)

I am working with Excel 2013 (my work computer just recently got updated...)


Thank you again, greatly appreciated.


Report •

#3
December 7, 2016 at 07:01:31
For what's worth, I'm not getting any errors.

I put Data Validation drop downs in Column I of both sheets, with Declined and Accepted as my choices.

When I chose Declined in Current Quotes, it copies the Row to Virtual Quotes. When I choose Accepted, nothing happens.

When I chose Accepted in Virtual Quotes, it copies the Row to Follow Up. When I choose Declined, nothing happens.

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


Report •

Related Solutions

#4
December 7, 2016 at 07:24:37
Thanks Derby for verifying the code works.

deesarah,

the code also works for me so it must be something that you are doing, or something with the formatting of your workbook. In column I do you have a data validation list, ie a drop down where you and select either 'Accepted' or "Declined" or are you manually typing the words in?

Also double check your sheets names and ensure they are exactly as you provided them in your original post.


Report •

#5
December 7, 2016 at 08:14:01
Hi Guys,

I made a new spread sheet and it worked!! THANK YOU!

I did have data-validations is column I. My original document was formatted as a table - which was probably the issue. Everything works SO perfectly now.

I greatly appreciate the help! - Cheers! Happy Holidays :)

message edited by deesarah


Report •

#6
December 8, 2016 at 00:20:45
deesarah,

that's brilliant glad you managed to figure it out an the code works for you. Thanks also for updating the thread, it is useful to know why it originally didn't work and how you managed to get it working.

Happy Holidays.


Report •

Ask Question