Copy unique rows based on two cell values

**ArtySin** · 02-14-2014, 07:58 PM

I have a sheet named 'Sales' where invoice data is stored. In cell 'A16' is a list of the customers from which I want to select a customer and then copy all rows where that customer's name is and also where the 'Paid' cell value is set to 'No'. I want to copy it to the 'Statements' sheet from cell 'C14' downward. I've managed to hack some code I've found and this currently will copy all the rows to the 'Statements2' sheet but not based on the customer's name or whether they've paid. I've attached the workbook.
Many thanks

**alansidman** · 02-14-2014, 08:23 PM

@ArtySin
When I downloaded your file a virus was attached as a temp file. Fortunately, Norton spotted it and gave it the heave-ho. You may want to check your files with an anti-virus program.

Working on your problem. Will post separately.

Alan

**ArtySin** · 02-15-2014, 09:42 AM

Originally Posted by alansidman

@ArtySin
When I downloaded your file a virus was attached as a temp file. Fortunately, Norton spotted it and gave it the heave-ho. You may want to check your files with an anti-virus program.

Working on your problem. Will post separately.

Alan

Alan, I keep my files on Dropbox so I was surprised you found a virus. I scanned them anyway but found nothing so rather strange.

**martindwilson** · 02-14-2014, 08:27 PM

strange i just scanned it with avg and superantispyware and its fine

**alansidman** · 02-14-2014, 08:29 PM

I guess I was just lucky.

Anyway, ArtySin, I think that a solution for you would be to employ the Advanced Filter function.

Here is a link to how to do it.

http://www.contextures.com/xladvfilter01.html

**AB33** · 02-15-2014, 02:06 PM

Like Alan, I have also Norton and was warned to remove the file.

**ArtySin** · 02-17-2014, 11:51 AM

@Alan, Looked at the advanced filters link and all works fine. However, a problem arises when converting this to vba as the person who will run this is a bit of a technophobe which is why I wanted to do this using vba assigned to a macro button. For example vba created to copy the filtered data may look like:

Sub copyFltr()
    Range("A8:E14").Select
    Selection.Copy
    Sheets("Statements").Select
    Range("C14").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

but when a different customer is selected Range("A8:E14") will change to e.g. Range("A3:E14") so the first few rows will not get copied over. The s/sheet I uploaded is just a smaller version of a much larger sheet where there are over 200 rows of invoice data so this problem will be worse. If you Guys could come up with some code to lookup the customer's name and only copy those rows over to the statements sheet. it would be much appreciated.
Many thanks

**alansidman** · 02-17-2014, 02:03 PM

I am unable to help you. I tried to download your file again in order to write some code and Norton removed it as viral. Hopefully, you can fix this and repost the file or someone else will be able to help you.

Alan

**martindwilson** · 02-17-2014, 02:07 PM

maybe norton is detecting false positives in excel 2011 files? any way the op has Excel 20011 so maybe norton has never heard of that

here it is again downloaded and re-saved in excel 2007

**alansidman** · 02-17-2014, 02:09 PM

@Martin;

**martindwilson** · 02-17-2014, 02:25 PM

hmm
http://www.symantec.com/security_res...121911-4650-99

**alansidman** · 02-17-2014, 04:24 PM

Thanks to Martin, I was able to get a clean copy of the file

Here is the code that should do it for you. I did move your criteria combobox to I1:I2 on the Sales Sheet. I would also suggest that you change the combo box on the invoice sheet to a formula that reads =Sales!I2

Sub FilterC()
'Filter Copy and Paste
'AMSidman 2/17/14

    Dim w1 As Worksheet
    Set w1 = Sheets("Sales")
    Dim w2 As Worksheet
    Set w2 = Sheets("Statements")
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim sCrit As String
    sCrit = Range("I2").Value
    Dim c As Range

    Application.ScreenUpdating = False
    With w1
        .AutoFilterMode = False
        .Range("A1:G" & lr).AutoFilter Field:=6, Criteria1:=sCrit
        .Range("A1:G" & lr).AutoFilter Field:=7, Criteria1:="No"
        .Range("A2:E" & lr).SpecialCells(xlCellTypeVisible).Copy
        w2.Range("C14").PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    fRng.AutoFilter
    Application.ScreenUpdating = True
    MsgBox ("Action Completed")
    
End Sub

Copy unique rows based on two cell values

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Re: Copy unique rows based on two cell values

Thread Information

Users Browsing this Thread

Similar Threads

Compare two sheets,copy and paste unique rows based on values in 2 columns

copy rows based on cell values

[SOLVED] Macro - Copy and Combine values in cell and paste it in other cells based on Unique ID

Copy Rows and Paste Based On Cell Values

[SOLVED] Can I automate copy of rows based on cell values

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions