Not allow macro to add duplicate values

AshaG

New Member
Joined
Jan 13, 2019
Messages
5
I'm currently using a macro to copy over certain data from one workbook to my main workbook.

Code:
Dim wsMaster As Workbook, xlsFiles As WorkbookDim Filename As String
Dim File As Integer
Dim r As Long
Dim s As Long
Dim rngValue As Range


 
Public Sub InputPR()
     
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
     
With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = True
    .Title = "Select Prison Release to Add"
    .Show
         
    If .SelectedItems.Count = 0 Then Exit Sub
         
    Set wsMaster = Workbooks("Requests.xlsm")
         
    For File = 1 To .SelectedItems.Count
             
        Filename = .SelectedItems.Item(File)
             
        If Right(Filename, 4) = ".xls" Or Right(Filename, 5) = ".xlsx" Then
                 
           Workbooks.Open Filename, 0, True
                 
           Set xlsFiles = ActiveWorkbook
           
           ' Case number
           xlsFiles.Sheets("Sheet1").Range("H2", Range("H" & Rows.Count).End(xlUp)).Copy
           wsMaster.Sheets("Requests").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial _
           Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
           
           ' Name
           xlsFiles.Sheets("Sheet1").Range("B2", Range("B" & Rows.Count).End(xlUp)).Copy
           wsMaster.Sheets("Requests").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial _
           Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
           
           ' MNI
           xlsFiles.Sheets("Sheet1").Range("A2", Range("A" & Rows.Count).End(xlUp)).Copy
           wsMaster.Sheets("Requests").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).PasteSpecial _
           Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
           
           With wsMaster.Sheets("Requests")
           Set rngValue = .Cells(Rows.Count, "D").End(xlUp).Offset(1, 0)
           rngValue.Value = "PR"
           End With


         
           xlsFiles.Application.CutCopyMode = False
           xlsFiles.Close SaveChanges:=False 'close without saving
                 
        End If
             
    Next File 'go to the next file and repeat the process
         
End With
     
Set wsMaster = Nothing
Set xlsFiles = Nothing
     
With Application
    .ScreenUpdating = True
    .EnableEvents = True
         
End With
     
End Sub

My current problem is I need to stupid proof to workbook (as many different people will be inputting data both manually and using the a button with the above macro) and I need to alter the above to not allow duplicate entries of the same data. I did have it set up to just delete the rows of any duplicate data, but I have conditional formatting colouring the rows and deleting rows always messes with the formatting. Is there a way to have it check the main Request sheet before pasting over the values from Sheet1? Or is there a better way?

Thanks in advance
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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
Back
Top