VBA: column comparison and sheet comparison with delete

[koz50]

Hi, new to the forum and have been researching different VBAs to use. I've tried to combine a few so I am not sure if this can be done or maybe I shouldn't bother with this whole scenario as it could be too long.

Here is my scenario:
I have a master sheet of data. I used a VBA to compare my input list of new data and it adds the new entries to the master list.
On the master list, I use it to add notes and I select from a drop-down in column E for a selection then I used a VBA to call out the data in column E and move to the respective sheet based on my selections. (I end up with 8 worksheets).

What I need is to do is a comparison of what get moves to the respective sheet and delete from the master. The master will then stay up to date with only new input data and for me to make my drop-down selections and add notes. I use the other 7 worksheets to have as my working sheets for those specific selections. I do have to be careful with comparisons because new input data could be dynamic for column A but data in column B & C may be different from the first entry.

Any input would be greatly appreciated! I would like to be advised on how to combine my VBAs because right now I put them in different modules to save and have separate. I am afraid combining them right now. If a test file would help, I can attach one.

 

[vw412]

I would need more detail about how the master is getting updated and what triggers the move to another sheet. When you say data in column A is dynamic, how so? Is dynamic only in Master or also in other sheets? Do you have to select the data to move to other sheet or do you want to be automated?

For you last question, help to combine you VBA code will require more intimate knowledge of the workbook and your use of it. All can be done if you want it.

You might upload an XL2BB version of your workbook and perhaps post your code. If you have a Dropbox (or something similar) that you can use to share your workbook that might be helpful also.

 

[koz50]

I would need more detail about how the master is getting updated and what triggers the move to another sheet. When you say data in column A is dynamic, how so? Is dynamic only in Master or also in other sheets? Do you have to select the data to move to other sheet or do you want to be automated?

For you last question, help to combine you VBA code will require more intimate knowledge of the workbook and your use of it. All can be done if you want it.

You might upload an XL2BB version of your workbook and perhaps post your code. If you have a Dropbox (or something similar) that you can use to share your workbook that might be helpful also.

 

[koz50]

I uploaded images of how the sheet looks. Right now, the master is getting updated from the first vba code, I copy the data in to the input sheet and the master pulls the new entries. Maybe I worded incorrectly on the data being dynamic. It'll stay constant as I work. However, Column A is most important and new entries will appear when copied to the input sheet and a source in column A could show up again but be different data in the respective columns B-D.

Sub DetectionsMatches()
'using to check for matches with input and master list
Application.ScreenUpdating = False

Dim ar As Integer
Dim ar1 As Integer
Dim ttt As Integer
Dim foundtrue As Boolean

ar = Sheets("Input").Cells(Sheets("Input").Rows.Count, "A").End(xlUp).Row
arl = Sheets("Detections").Cells(Sheets("Detections").Rows.Count, "A").End(xlUp).Row
ttt = Sheets("Detections").Cells(Sheets("Detections").Rows.Count, "A").End(xlUp).Row

'checking to see if column A matches column A in input list
For i = 1 To ar
foundtrue = False
For Z = 1 To arl
If Sheets("Input").Cells(i, 2).Value = Sheets("Detections").Cells(Z, 2).Value Then
foundtrue = True
Exit For
End If
Next Z
Application.ScreenUpdating = True
Application.ScreenUpdating = False
'if no matches then copies to master sheet of detections
If Not foundtrue Then
Sheets("Input").Rows(i).copy Destination:=Sheets("Detections").Rows(ttt + 1)
ttt = ttt + 1
End If
Next i
Application.ScreenUpdating = True
End Sub


This is how i am pulling based on drop-down but it is just duplicating. I know where I think it should go for deleting and comparing...but haven't added to the code.

Sub Calloutbasedonvalue()
'using to call out data of column E and pushing to respective sheets

Dim xRg As Range
Dim a As Long
Dim b As Long
Dim c As Long
Dim G As Long
Dim E As Long
Dim F As Long
Dim j As Long


a = Worksheets("Bolster_Detections").UsedRange.Rows.Count
b = Worksheets("Suspicious").UsedRange.Rows.Count
c = Worksheets("Resolved").UsedRange.Rows.Count
G = Worksheets("Pending").UsedRange.Rows.Count
E = Worksheets("Chapter").UsedRange.Rows.Count
F = Worksheets("Squat").UsedRange.Rows.Count
j = Worksheets("Completed").UsedRange.Rows.Count

'calling on suspicious list
If b = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Suspicious").UsedRange) = 0 Then b = 0
End If

Set xRg = Worksheets("Bolster_Detections").Range("E3:E" & a)
On Error Resume Next
Application.ScreenUpdating = False

For b = 1 To xRg.Count
If CStr(xRg(b).Value) = "Suspicious" Then
xRg(b).EntireRow.copy Destination:=Worksheets("Suspicious").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next

'move to offline/complete sheet
Application.ScreenUpdating = True

If b = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Completed").UsedRange) = 0 Then b = 0
End If

Set xRg = Worksheets("Suspicious").Range("E3:E" & b)
On Error Resume Next
Application.ScreenUpdating = False

For b = 1 To xRg.Count
If CStr(xRg(b).Value) = "Offline/Completed" Then
xRg(b).EntireRow.copy Destination:=Worksheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next

'comparison and remove step inserted here


Application.ScreenUpdating = True

'calling on chapter selection
If E = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Chapter").UsedRange) = 0 Then E = 0
End If

Set xRg = Worksheets("Bolster_Detections").Range("E3:E" & a)
On Error Resume Next
Application.ScreenUpdating = False

For E = 1 To xRg.Count
If CStr(xRg(E).Value) = "Chapter" Then
xRg(E).EntireRow.copy Destination:=Worksheets("Chapter").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next

'move to offline/complete sheet
Application.ScreenUpdating = True


'comparison and remove step inserted here

'calling on squat section
If F = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Squat").UsedRange) = 0 Then F = 0
End If

Set xRg = Worksheets("Bolster_Detections").Range("E3:E" & a)
On Error Resume Next
Application.ScreenUpdating = False

For F = 1 To xRg.Count
If CStr(xRg(F).Value) = "Squat" Then
xRg(F).EntireRow.copy Destination:=Worksheets("Squat").Range("A" & Rows.Count).End(xlUp).Offset(1)

'move to offline/complete sheet

End If
Next
Application.ScreenUpdating = True

'comparison and remove step inserted here


'calling on pending selection
If G = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Pending").UsedRange) = 0 Then G = 0
End If

Set xRg = Worksheets("Bolster_Detections").Range("E3:E" & a)
On Error Resume Next
Application.ScreenUpdating = False

For G = 1 To xRg.Count
If CStr(xRg(G).Value) = "Pending" Then
xRg(G).EntireRow.copy Destination:=Worksheets("Pending").Range("A" & Rows.Count).End(xlUp).Offset(1)

'move to offline/complete sheet inserted here

End If
Next

Application.ScreenUpdating = True

'comparison and remove step inserted here


'calling on resolved selection
If c = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Resolved").UsedRange) = 0 Then c = 0
End If

Set xRg = Worksheets("Bolster_Detections").Range("E3:E" & a)
On Error Resume Next
Application.ScreenUpdating = False

For c = 1 To xRg.Count
If CStr(xRg(c).Value) = "Resolved" Then
xRg(c).EntireRow.copy Destination:=Worksheets("Resolved").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next
Application.ScreenUpdating = True

'runs if completed or offline marked on master list
If j = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Completed").UsedRange) = 0 Then j = 0
End If

Set xRg = Worksheets("Bolster_Detections").Range("E3:E" & a)
On Error Resume Next
Application.ScreenUpdating = False

For j = 1 To xRg.Count
If CStr(xRg(j).Value) = "Offline/Completed" Then
xRg(j).EntireRow.copy Destination:=Worksheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1)
Application.ScreenUpdating = True

End If
Next

End Sub