Running a looping macro on another sheet within the same macro help "For control variable in use"

[ConUJon]

I am running quite a complex code at the moment to analyze employee id's in a set of teams. The road block I am facing is that my code has to run an autofilter for an unknown amount of unique filters, copy the column A on Sheet 1, and then paste into my data (2) sheet in Column A. From there I need it to run the same macro on Sheet 2, and then paste the data from column 1 into my data sheet in column B.

At this point it's a simple copy and paste the results to a master sheet. I am running into error codes and I am lost as what to do.... Is running the same macro inside on another sheet impossible? I will list the macro in steps to make it easy...

Run Macro On Sheet 1:

Sub test()

    Dim DataRange As Range
    Dim UniqueRng As Range
    Dim Cell As Range
    Dim LastRow As Long
    Dim LastColumn As Long
    
    Application.ScreenUpdating = False

    With Worksheets("July 1 HC")
        With .UsedRange
            LastRow = .Rows.Count + .Rows(1).Row - 1
            LastColumn = .Columns.Count + .Columns(1).Column - 1
        End With
        Set DataRange = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
        DataRange.Columns(3).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=.Cells(1, LastColumn + 2), unique:=True
        Set UniqueRng = .Range(.Cells(2, LastColumn + 2), .Cells(.Rows.Count, LastColumn + 2).End(xlUp))
        For Each Cell In UniqueRng
            DataRange.AutoFilter field:=3, Criteria1:=Cell
            DataRange.Copy
            Worksheets.Add
            ActiveSheet.Name = Cell
            Range("A1").PasteSpecial
            Columns("A:A").Select
            Selection.Copy
            Sheets("DATA (2)").Select
            Columns("A:A").Select
            ActiveSheet.Paste
            Range("A1").Select
            Application.CutCopyMode = False
            ActiveCell.FormulaR1C1 = "Employee ID July"
        Next Cell
        .AutoFilterMode = False
        .Cells(1, LastColumn + 2).EntireColumn.Delete
        .Activate
    End With
        
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
    
End Sub

What I want to do with the data:

Sub RUN_ANALYSIS()
'
' RUN_ANALYSIS Macro
'

'
    Sheets("DATA (2)").Select
    ActiveSheet.Range("$A$1:$D$1045988").AutoFilter Field:=4, Criteria1:="No"
    Range("I1:O1").Select
    Selection.Copy
    Sheets("Results 2.0").Select
    Cells(Range("B100000").End(xlUp).Row + 1, 2).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
End Sub

NOW THIS IS MY ATTEMPT OF RUNNING THE CODE TOGETHER WITH SHEET 2 (August 1 HC) IN THE 1ST MACRO

Option Explicit

Sub test()

    Dim DataRange As Range
    Dim UniqueRng As Range
    Dim Cell As Range
    Dim LastRow As Long
    Dim LastColumn As Long
    
    Application.ScreenUpdating = False

    With Worksheets("July 1 HC")
        With .UsedRange
            LastRow = .Rows.Count + .Rows(1).Row - 1
            LastColumn = .Columns.Count + .Columns(1).Column - 1
        End With
        Set DataRange = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
        DataRange.Columns(3).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=.Cells(1, LastColumn + 2), unique:=True
        Set UniqueRng = .Range(.Cells(2, LastColumn + 2), .Cells(.Rows.Count, LastColumn + 2).End(xlUp))
        For Each Cell In UniqueRng
            DataRange.AutoFilter Field:=3, Criteria1:=Cell
            DataRange.Copy
            Worksheets.Add
            ActiveSheet.Name = Cell
            Range("A1").PasteSpecial
            Columns("A:A").Select
            Selection.Copy
            Sheets("DATA (2)").Select
            Columns("A:A").Select
            ActiveSheet.Paste
            Range("A1").Select
            Application.CutCopyMode = False
            ActiveCell.FormulaR1C1 = "Employee ID July"
        With Worksheets("August 1 HC")
            With .UsedRange
                    LastRow = .Rows.Count + .Rows(1).Row - 1
                    LastColumn = .Columns.Count + .Columns(1).Column - 1
        End With
        Set DataRange = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
        DataRange.Columns(3).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=.Cells(1, LastColumn + 2), unique:=True
        Set UniqueRng = .Range(.Cells(2, LastColumn + 2), .Cells(.Rows.Count, LastColumn + 2).End(xlUp))
        For Each Cell In UniqueRng
        DataRange.AutoFilter Field:=3, Criteria1:=Cell
            DataRange.Copy
            Worksheets.Add
            ActiveSheet.Name = Cell
            Range("A1").PasteSpecial
            Columns("B:B").Select
            Selection.Copy
            Sheets("DATA (2)").Select
            Columns("B:B").Select
            ActiveSheet.Paste
            Range("B1").Select
            Application.CutCopyMode = False
            ActiveCell.FormulaR1C1 = "Employee ID August"
            Sheets("DATA (2)").Select
            ActiveSheet.Range("$A$1:$D$1045988").AutoFilter Field:=4, Criteria1:="No"
            Range("I1:O1").Select
            Selection.Copy
            Sheets("Results 2.0").Select
            Cells(Range("B100000").End(xlUp).Row + 1, 2).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Next Cell
        .AutoFilterMode = False
        .Cells(1, LastColumn + 2).EntireColumn.Delete
        .Activate
    End With
        
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
    
End Sub