Hmmm.... That code seems to just copy over the data from worksheet 2 to the master sheet. Is it removing duplicates along the way? In case I was unclear in my explanation, I'll attempt to explain in an organized manner:
  1. Step 1: I receive a list of data with 65 columns and up to a 1000 rows of data. (many times a dozen or so of the rows won't have data in every column)
  2. Step 2: I open the new list received and run the following code to clean up the data and keep only the relevant fields.
    Code:
    Sub MailOrganize()
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("B:B").Select
    Selection.Copy
    Columns("C:C").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Set rngTemp1 = Range("B:B")
    With Range("A1,B1,C1,D1,E1,F1,G1,H1,I1,J1").Font
        .Bold = True
    End With
    
    Range("A1").Value = "SITE"
    Range("B1").Value = "TEMP_1"
    Range("C1").Value = "TEMP_2"
    Range("D1").Value = "ADDRESS_1"
    Range("E1").Value = "UNIT"
    Range("F1").Value = "CITY"
    Range("G1").Value = "STATE"
    Range("H1").Value = "ZIP"
    Range("I1").Value = "FIRST"
    Range("J1").Value = "LAST"
    
    Set rngTemp1 = Range("B1:B1000")
'    On Error GoTo SkipTemp1
    rngTemp1.SpecialCells(xlCellTypeBlanks).Select
    Dim l1Cell
    For Each l1Cell In Selection
    l1Cell.Value = " --- "
    Next
    
'SkipTemp1:
    Set rngTemp2 = Range("C1:C1000")
'    On Error GoTo SkipTemp2
    rngTemp2.SpecialCells(xlCellTypeBlanks).Select
    Dim l2Cell
    For Each l2Cell In Selection
    l2Cell.Value = " --- "
    Next
    
'SkipTemp2:
    Set rngSite = Range("A1:A1000")
'    On Error GoTo SkipSite
    rngSite.SpecialCells(xlCellTypeBlanks).Select
    Dim sCell
    For Each sCell In Selection
    sCell.Value = " --- "
    Next
    
'SkipSite:
    Set rngName = Range("I1:I1000")
'    On Error GoTo SkipName
    rngName.SpecialCells(xlCellTypeBlanks).Select
    Dim nameCell
    For Each nameCell In Selection
    nameCell.Value = " --- "
    Next
    
'SkipName:

End Sub
  3. Step 3: In case the list didn't have a complete 1 thousand entries, I would clear the contents from any "---" marks continuing past the data down to the 1000 row.
  4. Step 4: I would then close the list and open the Master workbook.
  5. Step 5: I would run the following import script:
    Code:
    Sub BulkImport()

   Sheets(1).Select
   Dim Srt As Worksheet
   Set Srt = ActiveSheet
   Dim InFileNames As Variant
   Dim fCtr As Long
   Dim tempWkbk As Workbook
   Dim consWks As Worksheet
   Dim szToday As String
   szToday = Format(Date, "mm-dd-yy")
   Sheets.Add
   ActiveSheet.Move After:=Srt
   ActiveSheet.Name = szToday
   Set consWks = ActiveWorkbook.Sheets(2)
   InFileNames = Application.GetOpenFilename _
   (FileFilter:="Excel Files, *.*", MultiSelect:=True)
   Application.ScreenUpdating = False
   If IsArray(InFileNames) Then
      For fCtr = LBound(InFileNames) To UBound(InFileNames)
        With Workbooks.Open(Filename:=InFileNames(fCtr))
                MsgBox "Number of cells that have data: " & Application.WorksheetFunction.CountA(.Sheets(1).Range("A2:J" & .Sheets(1).Range("A" & .Sheets(1).Rows.Count).End(xlUp).Row))
            .Sheets(1).Range("A1:J" & .Sheets(1).Range("A" & .Sheets(1).Rows.Count).End(xlUp).Row).Copy consWks.Range("A" & consWks.Rows.Count).End(xlUp)(1)
            .Close 0
        End With
      Next fCtr
   Else
      MsgBox "No file selected."
   End If
   With Application
      .StatusBar = True
      .ScreenUpdating = True
   End With
   
End Sub
  6. Step 6: I would then need to compare the new list to the list on the master sheet. I would need to find duplicates based on the members' addresses. The members' addresses will be listed in Column D. Should it find a duplicate, it would mark all the duplicates (preferably the entire row).
  7. Step 7: I would then need to remove the duplicates after assessing that they really are duplicates since sometimes clients have similar addresses but with a different unit or suite number. One way I thought of is to have the duplicates marked on the temp worksheet, and copied to a new worksheet where the entire row with the duplicate found would be a green color background while the entire row being checked against from the master sheet would be copied underneath it to the new worksheet, and would have a yellow background. Although, I'm not sure if it's attainable through the code to do.
  8. Step 8: I would then remove the duplicates from the temp worksheet.
  9. Step 9: I would copy the new unique data over to the master sheet to the bottom, continuing the growth of the sheet. Since all the headers are the same, I would not need the first row from the temp worksheet to be copied over to the master sheet.


I really really appreciate it. I can't stress that enough. Thank you for all the help you can give.
If there is anything else needed from me, please let me know.

Respectfully,

Amit