Any issues with this?

[Glory]

Sub CompileList()
 
'Building lists in Sheet4, columns A-C
'Source is Sheets1-3, column A
 
For e = 1 To 3
    theRow3 = 1
    If e = 1 Then: Set a = ThisWorkbook.Sheets("Sheet1")
    If e = 2 Then: Set a = ThisWorkbook.Sheets("Sheet2")
    If e = 3 Then: Set a = ThisWorkbook.Sheets("Sheet3")
    Set b = ThisWorkbook.Sheets("Sheet4")
    theRow1 = a.Range("A" & a.Range("A" & Rows.Count).End(xlUp).Row).Row
    If theRow1 < 2 Then: Exit Sub
    For c = 2 To theRow1
        theRow2 = b.Cells(b.Cells(Rows.Count, e).End(xlUp).Row, e).Row
        
        If theRow2 < 2 Then: theRow2 = 2
        For d = 2 To theRow2
            If a.Cells(c, 1) = b.Cells(d, e) Then
                    GoTo theNext1
            End If
        Next
        theRow3 = theRow3 + 1
        b.Cells(theRow3, e) = a.Cells(c, 1)
theNext1:
    Next
Next
 
'Building list in Sheet4, column E
'Source is Sheet4, columns A-C

theRow3 = 2

For e = 1 To 3
theRow1 = b.Cells(b.Cells(Rows.Count, e).End(xlUp).Row, e).Row
If theRow1 < 2 Then: Exit Sub
    For c = 2 To theRow1
        theRow3 = b.Cells(b.Range("E" & Rows.Count).End(xlUp).Row, e).Row
        For d = 2 To theRow3
            If b.Cells(c, e) = b.Cells(d, 5) Then
                GoTo theNext2
            End If
        Next
    b.Cells(theRow3 + 1, 5) = b.Cells(c, e)
theNext2:
    
    Next
Next
End Sub

Sheets 1-3 have contents in column A. Many duplicates are present.

The code copies non-duplicate entries to sheet4.

Column A in Sheet1 fills column A in Sheet4.

Column A in Sheet2 fills column B in Sheet4.

Column A in Sheet3 fills column C in Sheet4.

When the first three loops are finished, the code moves on, parsing the three lists in sheet four to produce a final list of non-duplicate entries in column E of sheet4.

I'm wondering if there are any obvious problems that I'm missing that are going to crop up and cause issues with lost information later on.

 

[Glory]

Bump.

 

[Norie]

Have you tried doing the unique values part with Advanced Filter?

 

[Glory]

Nope, I didn't even know what this was.

It says I can only copy filtered data to the active sheet.

I was going to record myself doing it then incorporate the macro into my code. Should I just use all the cells in the desired column as my "List Range" if I do it this way? I was using End(xlUp) to prevent massive lag when iterating through it.

 

[Norie]

Glory

Normally you can't copy the results of an Advanced Filter to another worksheet when you do it manually, but you can in code.

Mind you, what I was thinking was you could copy the data from the other sheets to column A of sheet 4.

Then filter the copied data to column E of sheet 4 with the Unique Values option.

All of that could be done with code, and you could even use a temporary worksheet for part of it.

 

[Glory]

Would you be willing to provide me with a skeletal example of what you're talkign about? I'd prefer to do it entirely through VBA if I possibly could, but if I can't record the event or I don't know the method then I can't get started.

Without copying the data initially, filtering column A of sheet1 to preserve only unique data, where the destination is column A of sheet4.