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!

 

[Joe4]

Welcome to the Board!

See if this does what you want (be sure to save a backup of your file first!):

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, _
        key2:=Range("A1"), order2:=xlAscending, Header:=xlYes
    Sheets("Summary").Select
    Range("A1").CurrentRegion.Sort _
        key1:=Range("O1"), order1:=xlDescending, _
        key2:=Range("A1"), order2:=xlAscending, Header:=xlYes
  
'   Delete "New" sheet
    Application.DisplayAlerts = False
    Sheets("New").Delete
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True

    MsgBox "Macro complete!"

End Sub

 

[514ce]

It appears I get an error when it trys to start sorting data in the "Baseline" sheet. The error starts when it reaches the following code.

Range("A1").CurrentRegion.Sort _
key1:=Range("O1"), order1:=xlDescending, _
key2:=Range("A1"), order2:=xlAscending, Header:=xlYes

 

[Joe4]

Can you post a sample of what your the first few lines of what the data on your "Baseline" sheets look like?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[514ce]

Below is a sample of the "Baseline" sheet.

Person Id

Full Name

Business Email Address

Hire Date

Business Title

Job Title

Job Code

Job Function (Label)

L2

Business Unit (Label)

Region

Location (Name)

Report Run Date

Division (Label)

Effective Start Date

Event (Picklist Label)

80018355​

Smith, John

john.smith@yahoo.com

4/1/2002​

Leader

Supervisor

SA0270

Production

Smith, Jane

Supply Chain

SAM

Brazil

11/20/2021​

FC

12/11/2021​

Data Change

 

[Joe4]

Did you copy and paste my code exactly "as-is", or did you try typing it? Always use copy/paste to eliminate the chance of any typos.

Also, do you have any merged cells or protected ranges on your "Baseline" sheet?

Is your header row in row 1?

Do you have any completely blank lines in the middle of your data?

 

[514ce]

Did you copy and paste my code exactly "as-is", or did you try typing it? Always use copy/paste to eliminate the chance of any typos.

Also, do you have any merged cells or protected ranges on your "Baseline" sheet?

Is your header row in row 1?

Do you have any completely blank lines in the middle of your data?

• I did copy and paste the code in
• There are no merged cells or protected ranges (I went ahead and double checked as well)
• The header is on row 1
• There are no blank rows in the middle of the data

 

[Joe4]

Do you have any errors being returned in columns A or O?

Also, I should have asked initially, what is the exact error message that is being returned?

 

[514ce]

• I do not see any errors in those columns
• When running the code I get a Run-time error '1004': Sort method of Range class failed.

 

[Joe4]

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)