Copy rows from Sheet2 that don't exist on Sheet1

xcl_usr

New Member
Joined
Jan 19, 2005
Messages
2
Daily I get cases added to my queue. I would like to take my case dump (exported to Excel) and intigrate it to a formated sheet that I work from during the day. I don't have a way to just grab new cases in the export. I can paste the export to Sheet2. I'll use a macro to delete unneeded columns, then I need to walk Sheet2 and if it does not exist in the list of Col1 on Sheet1, then copy it to Sheet1. After which I will use a macro to sort and add conditional formattitng on Sheet1.

Exported worksheet (Pasted to Sheet2)
-----------------------
Col1 Col2 Col3 Col4
100 P2 W Desc1
101 P3 W Desc2
102 P1 W Desc3

Formatted worksheet (Sheet1)
-----------------------
Col1 Col2 Col3 Col4 Notes
98 P2 W DescA Escalate
99 P2 H DescB Call Back
100 P2 W Desc1 Send email
101 P3 W Desc2 Sent email

Note that record 100 and 101 exist already on Sheet1, so I don't want to copy them from Sheet2.

Bonus points - Add new rows, and update just certain cells Col2 changed, and retain special cells on Sheet1 like Notes.

Help!

Tony
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
Something like this?

Code:
Option Explicit

Sub TransferNewData()
Const strCol1 = "A" 'assumes columns 2-4 are directly to the right of 1
Const strSheet1Name = "Sheet1"
Const strSheet2Name = "Sheet2"
Const lngFirstRowDataSheet1 = 1
Const lngFirstRowDataSheet2 = 1

Dim lngLastRowSht1 As Long, lngLastRowSht2 As Long
Dim sht1 As Worksheet, sht2 As Worksheet
Dim strOrigSheet As String, strOrigSelectionSht1 As String, strOrigSelectionSht2 As String
Dim cell As Range
Dim varMatch

    Application.ScreenUpdating = False
    strOrigSheet = ActiveSheet.Name
    Set sht1 = Sheets(strSheet1Name)
    Set sht2 = Sheets(strSheet2Name)
    
    sht1.Select
    strOrigSelectionSht1 = Selection.Address
    sht2.Select
    strOrigSelectionSht2 = Selection.Address
    
    lngLastRowSht1 = sht1.Range(strCol1 & 65536).End(xlUp).Row
    lngLastRowSht2 = sht2.Range(strCol1 & 65536).End(xlUp).Row
    
    sht2.Select
    For Each cell In sht2.Range(strCol1 & lngFirstRowDataSheet2 & ":" & strCol1 & lngLastRowSht2)
        On Error Resume Next
        varMatch = WorksheetFunction.VLookup(cell, sht1.Range(strCol1 & lngFirstRowDataSheet1 & ":" & strCol1 & lngLastRowSht1), 1, False)
        If Err <> 0 Then
            Err.Clear
            On Error GoTo 0
            sht2.Range(cell, cell.Offset(0, 3)).Select
            Selection.Copy
            lngLastRowSht1 = lngLastRowSht1 + 1
            sht1.Select
            sht1.Range(strCol1 & lngLastRowSht1).Select
            Selection.PasteSpecial Paste:=xlPasteValues
            sht2.Select
        End If
    Next
    
    On Error GoTo 0
    
    sht1.Select
    Range(strOrigSelectionSht1).Select
    sht2.Select
    Range(strOrigSelectionSht2).Select
    
    Set sht1 = Nothing
    Set sht2 = Nothing
    Sheets(strOrigSheet).Select
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Just goes through Col1 in sheet2 til it finds a value not in sheet1 in col1 then it copies and pastes the values (so as not not distrupt formatting), then continues on.
 

Forum statistics

Threads
1,147,626
Messages
5,742,214
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top