I'm currently using a macro to copy over certain data from one workbook to my main workbook.
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
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