Copy and Paste Rows Based on Multiple Criteria

[514ce]

First I want to say thank you, since I am new to the world of VBA / Macros. I currently have a workbook containing 3 worksheets named "Baseline", "New", and "Summary". Each month I will recieve a report and add it to this workbook and name it "New". All worksheets are laidout with the same column headers. I would like to compare rows in the "New" sheet with the "Baseline". I basically need to develop an AND IF logic in a macro to compare criteria by row across sheets. So if a row in sheet "New" does not match values for columns "A" and "O" in sheet "Baseline", I would like to copy and paste the non-matching row from sheet "New" to the first blank row in both the "Summary" and "Baseline" sheet. Column "A" is a numerical ID and column "O" is a date. After the rows are copied over, I want to automatically sort by date (column "O") from newest to oldest in both sheets "New" and "Baseline". Then finally delete the "New" sheet. Your help is much appreciated!

 

[514ce]

Hmmm, I don't see any reason why that should be failing.

Try this.
1. Go to the "Baseline" sheet
2. Select cell A1
3. Press the following three keys at the same time: CTRL, SHIFT, *
4. Tell me the exact address of the range that is then select (i.e. A1:Z100)

Prior to running the code, it is A1:P840. Remember each time the "Baseline" sheet will expand in size.

 

[Joe4]

Prior to running the code, it is A1:P840. Remember each time the "Baseline" sheet will expand in size.

Yes, CurrentRegion takes care of that. It dynamically select all cells in the current contiguous range.

I had you do that because I just wanted to make sure that it was going out far enough (at least to column O), which it is.

What happens if you try to do the following sort manually on your "Baseline" sheet?
Can you do a 2-level sort, so the initial sort is by column O in descending order, and then by column A is ascending order?

Can you do that manually without getting errors?
If not, then see what happens when you just try to sort by column O (and not include column A in your sort).
Does that work?

 

[514ce]

It

Yes, CurrentRegion takes care of that. It dynamically select all cells in the current contiguous range.

I had you do that because I just wanted to make sure that it was going out far enough (at least to column O), which it is.

What happens if you try to do the following sort manually on your "Baseline" sheet?
Can you do a 2-level sort, so the initial sort is by column O in descending order, and then by column A is ascending order?

Can you do that manually without getting errors?
If not, then see what happens when you just try to sort by column O (and not include column A in your sort).
Does that work?

The manual sorting does work proper. FYI, I was only wanting to sort by date (column "O") from newest to oldest in both sheets "New" and "Baseline".

 

[514ce]

I did notice that the data for the sheets "New" and "Baseline" are tables, will that make a difference?

 

[Joe4]

OK, see if this version works:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    Dim n As Double
    Dim d As Date
    Dim rngA As Range
    Dim rngO As Range
        
    Application.ScreenUpdating = False
  
'   Set search ranges from "Baseline" sheet
    Set rngA = Sheets("Baseline").Columns("A:A")
    Set rngO = Sheets("Baseline").Columns("O:O")
  
'   Find last row on "New Sheet" with data
    lr = Sheets("New").Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows on "New" sheet, starting with row 2
    For r = 2 To lr
'       Get number from column A and date from column O
        n = Sheets("New").Cells(r, "A")
        d = Sheets("New").Cells(r, "O")
'       See if no matching rows on "Baseline" sheet
        If Application.WorksheetFunction.CountIfs(rngA, n, rngO, d) = 0 Then
'           Copy row to bottom of "Basline" and "Summary" sheets
            Sheets("New").Rows(r).Copy Sheets("Baseline").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Sheets("New").Rows(r).Copy Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next r
  
'   Sort "Baseline" and "Summary" sheets
    Sheets("Baseline").Select
    Range("A1").CurrentRegion.Sort _
        key1:=Range("O1"), order1:=xlDescending, Header:=xlYes
    Sheets("Summary").Select
    Range("A1").CurrentRegion.Sort _
        key1:=Range("O1"), order1:=xlDescending, Header:=xlYes
  
'   Delete "New" sheet
    Application.DisplayAlerts = False
    Sheets("New").Delete
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True

    MsgBox "Macro complete!"

End Sub

 

[514ce]

OK, see if this version works:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    Dim n As Double
    Dim d As Date
    Dim rngA As Range
    Dim rngO As Range
       
    Application.ScreenUpdating = False
 
'   Set search ranges from "Baseline" sheet
    Set rngA = Sheets("Baseline").Columns("A:A")
    Set rngO = Sheets("Baseline").Columns("O:O")
 
'   Find last row on "New Sheet" with data
    lr = Sheets("New").Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows on "New" sheet, starting with row 2
    For r = 2 To lr
'       Get number from column A and date from column O
        n = Sheets("New").Cells(r, "A")
        d = Sheets("New").Cells(r, "O")
'       See if no matching rows on "Baseline" sheet
        If Application.WorksheetFunction.CountIfs(rngA, n, rngO, d) = 0 Then
'           Copy row to bottom of "Basline" and "Summary" sheets
            Sheets("New").Rows(r).Copy Sheets("Baseline").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Sheets("New").Rows(r).Copy Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next r
 
'   Sort "Baseline" and "Summary" sheets
    Sheets("Baseline").Select
    Range("A1").CurrentRegion.Sort _
        key1:=Range("O1"), order1:=xlDescending, Header:=xlYes
    Sheets("Summary").Select
    Range("A1").CurrentRegion.Sort _
        key1:=Range("O1"), order1:=xlDescending, Header:=xlYes
 
'   Delete "New" sheet
    Application.DisplayAlerts = False
    Sheets("New").Delete
    Application.DisplayAlerts = True
   
    Application.ScreenUpdating = True

    MsgBox "Macro complete!"

End Sub

So after running the new code I still get the same error. It occurs right after Sheets("Baseline").Select

 

[Joe4]

I did notice that the data for the sheets "New" and "Baseline" are tables, will that make a difference?

Ugh, yes. That is an absolutely critical detail that changes everything (would have been good to know up-front).

Data table work entirely differently. You need to insert new rows into your table to put new data into (so I am guessing that the new rows that are being copied over are probably being copied BELOW the current table in the "Baseline" table).

And I think you need to sort by the specificied field name in the table, instead of the Excel column letter.

Unless you can change your file so that the data in the Baseline sheet is NOT a table, and just a data list, the code will have to be re-written.
I would have to do research on that, as I really do not use Data tables in Excel (if I ever have need to use tables, I use Access instead of Excel).
So that would probably take a while when I have more time to research it.

 

[514ce]

Ugh, yes. That is an absolutely critical detail that changes everything (would have been good to know up-front).

Data table work entirely differently. You need to insert new rows into your table to put new data into (so I am guessing that the new rows that are being copied over are probably being copied BELOW the current table in the "Baseline" table).

And I think you need to sort by the specificied field name in the table, instead of the Excel column letter.

Unless you can change your file so that the data in the Baseline sheet is NOT a table, and just a data list, the code will have to be re-written.
I would have to do research on that, as I really do not use Data tables in Excel (if I ever have need to use tables, I use Access instead of Excel).
So that would probably take a while when I have more time to research it.

I changed the sheet "Baseline" so it is not a table any more and the code works perfect! Appreciate your help on this!

 

[Joe4]

You are welcome.
Glad you were able to do that (I really do not like messing around with Excel tables in VBA)!

Note: I changed the solution to the post that contains the final VBA code that was used. You usually want to mark the post that contains the solution used, so if someone were to look at the thread in the future, they could just look at the original question and solution.