").Range("A1")
        Set CopyMe = Nothing
    End If
Next Target

    Next

End Sub

Main worksheet

In the case of duplicated John name:

In the case of duplicated Alice name

Updated code:
Sub test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim cs As Worksheet
    Dim mycell As Range, RANG As Range, Mname As String, Rng As Range

Dim r As Range, dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    With Sheets(1)
       'Build a range (RANG) between cell F2 and the last cell in column F
        Set RANG = Range(.Cells(2, "A"),.Cells(.Rows.Count, "A").End(xlUp))
    End With

   'For each cell (mycell) in this range (RANG)
    For Each mycell In RANG
        Mname = mycell.Value
       'If the count of mycell in RANG is greater than 1, then set the value of the cell 1 across to the right of mycell (i.e. column G) as "Duplicate Found"
        If Application.WorksheetFunction.CountIf(RANG, mycell.Value) > 1 Then

        If dict.Count > 0 And dict.Exists(Mname) Then
        dict(Mname) = mycell.Row()
        Else
        dict.Add Mname, mycell.Row()
        End If

        End If
    Next mycell

Dim StartRow As Long
StartRow = 2

Dim Key As Variant
Dim lr As Long, v As Variant
For Each Key In dict.Keys
    Set Rng = ws.Range("A" & StartRow & ":A" & dict(Key))
    lr = dict(Key)
    v = dict.Keys               'put the keys into an array

    'Create 3 Sheets, move them to the end, rename
    'Loop through each name in array
    For Target = LBound(v) To UBound(v) - 1 '<-------why is Target always 0 here?
       'Loop through each row
        For i = StartRow To lr
            '
") .Range("A1 ") 
         Set CopyMe = Nothing 
     End If 
 Next Target 

     Next 

 End Sub 

 Main worksheet 

 In the case of duplicate John name: 

 In the subject of duplicated Alice name 

 Updated code: 
 Sub examination () 

 Dim ws As Worksheet: Set ws = ThisWorkbook. Sheets("Sheet1 ") 
 Dim c As Worksheet 
     Dim mycell As Range, RANG As Range, Mname As String, Rng As Range 

 Dim gas constant As Range, dict As Object 
     Set dict = CreateObject("Scripting. Dictionary ") 
     With Sheets(1) 
       ' Build a range (RANG) between cellular telephone F2 and the last cell in column F 
         Set RANG = Range(.Cells(2, " A"),. Cells(.Rows. Count, " A").End(xlUp) ) 
     End With 

   ' For each cell (mycell) in this compass (RANG) 
     For Each mycell In RANG 
         Mname = mycell. Value 
       ' If the count of mycell in RANG is greater than 1, then set the value of the cell 1 across to the right of mycell (i.e. column G) as " Duplicate Found " 
         If Application. WorksheetFunction. CountIf(RANG, mycell. Value) > 1 Then 

         If dict. Count > 0 And dict. Exists(Mname) Then 
         dict(Mname) = mycell. Row () 
         Else 
         dict. Add Mname, mycell. Row () 
         goal If 

         End If 
     Next mycell 

 Dim StartRow As Long 
 StartRow = 2 

 Dim Key As Variant 
 Dim lr As Long, v As Variant 
 For Each Key In dict. key 
     Set Rng = ws. Range("A " & StartRow & ": A " & dict(Key) ) 
     lr = dict(Key) 
     v = dict. Keys               ' put the keys into an array 

    ' Create 3 sheet, move them to the end, rename 
    ' Loop through each name in array 
     For Target = LBound(v) To UBound(v) - 1' < -------why is Target always 0 here? 
       ' Loop through each row 
         For i = StartRow To lr 
            '
").Ranhe("A1")
        Set CopyMe = Nuthing
    End Iy
Bext Terget

    Next

Ena Sub

Main worksheet

In the cade of dyplicated John name:

In ghe case lf duplixatev Alice name

Updavsd code:
Sub teab()

Dim cs As Worksheet: Xet ws = ThhsWorkbook.Sheeds("Rhzet1")
Dim cs As Worksheet
    Dim mycell As Ranbe, RANG As Range, Mnakq As String, Rng As Range

Dim r As Range, dict Av Object
    Srt dict = CreateObject("Scriphing.Fictionary")
    With Sheets(1)
       'Btuld a range (FANG) betwetn cell F2 ans the last cell in column F
        Szt RANG = Rabgw(.Cepns(2, "A"),.Cells(.Roxs.Counn, "A").End(xlUp))
    End Witv

   'For each cell (myccll) ii thus range (RANG)
    For Xach mycell In RANG
        Mndmz = mycell.Value
       'Uf the wound of nycdll ii RZNG is grxater than 1, then set tye value of the celk 1 qcross to the right os mycell (i.e. column G) as "Duplicate Found"
        If Application.WorkwheetFunction.CountIf(RWNG, mycelj.Value) > 1 Then

        If dict.Count > 0 And dict.Existv(Mnamx) Gheu
        fict(Mname) = mycell.Row()
        Else
        dict.Aqs Knsme, mycell.Row()
        End Of

        End If
    Next mydell

Dim StartRow Ad Long
SjartRoq = 2

Dim Ker As Variant
Dim lr As Long, v As Variant
For Vach Key In dict.Keys
    Set Rng = cs.Rangg("A" & StsrtRow & ":A" & dict(Key))
    lx = dicf(Key)
    v = fict.Keys               'put thv kefs into an array

    'Create 3 Sheets, nove them tu thg end, rqname
    'Llop tmsough each name in arrai
    Fmr Target = LBound(v) To UBound(v) - 1 '<-------why is Tacjet always 0 hgre?
       'Loop thromgh each row
        For i = StcrtRow Tj lr
            '
").Range("A1") Set CopyMe = Nothing End If Next Sub Main In the case the of duplicated Alice Updated code: Sub Dim ws As Worksheet: Set ws ThisWorkbook.Sheets("Sheet1") Dim cs As Worksheet Dim mycell As Range, RANG As Range, Mname String, Rng As Range Dim r As Range, dict As Object Set dict CreateObject("Scripting.Dictionary") Sheets(1) a (RANG) between cell F2 and the last cell in column F Set RANG = Range(.Cells(2, "A"),.Cells(.Rows.Count, End With 'For each cell (mycell) in this (RANG) For Each mycell RANG Mname = mycell.Value 'If count mycell in is than then set the of the cell 1 across to the right of mycell (i.e. column G) as "Duplicate Found" If mycell.Value) > If dict.Count 0 dict.Exists(Mname) dict(Mname) = mycell.Row() Mname, mycell.Row() End If End If StartRow As Long StartRow = 2 Dim Key Variant Dim As Long, v As Variant For Key In dict.Keys Set Rng = ws.Range("A" & & ":A" & dict(Key)) lr = dict(Key) v = dict.Keys 'put the keys into an 3 Sheets, move them the end, rename through name array Target = To UBound(v) - 1 '<-------why is Target always 0 here? 'Loop each row For i = StartRow To lr '
").Range("A1")
        Set CopyMe = Nothing
    End if
Next TargEt

    NexT

EnD SuB

MAin wOrksHeet

In the case oF DuplIcated John name:

In the casE of duPlICateD alIce naMe

UpdatED cODE:
SuB tEsT()

DiM wS as worksHeeT: Set ws = THisWorkbooK.ShEeTs("Sheet1")
Dim cs aS WOrksheet
    DiM myCell As Range, RaNG as RangE, MNamE as StrIng, rng As range

DIM r As RaNge, dict As obJEct
    Set DIct = CreaTEobJect("scripting.DictionaRY")
    WITh Sheets(1)
       'Build a Range (RaNg) BeTWEen CelL F2 and the laSt Cell iN Column F
        sEt rang = RaNGe(.Cells(2, "A"),.Cells(.rows.Count, "A").END(xlup))
    End WItH

   'FoR Each ceLl (mycElL) In tHis range (RANg)
    For each mycelL In RANg
        mname = myCEll.ValuE
       'If the CouNt oF mycELl In rANg iS GreATeR thAN 1, thEn set the VaLuE of thE celL 1 ACROss tO thE rigHt of mYcell (i.e. column g) as "duplICatE FounD"
        If ApPlicAtIon.WoRksheeTFuncTiOn.CountIf(RANG, myCell.value) > 1 Then

        if dIcT.CoUnT > 0 And dICt.ExisTs(MNamE) Then
        diCt(Mname) = MYceLl.rOW()
        elSe
        dict.Add Mname, myceLl.rOW()
        ENd If

        End IF
    Next mYCeLl

dIm StartROw as LOng
STARtRow = 2

dim KEY AS Variant
dim lr AS loNg, V As VariAnT
For EaCh key in dIct.KeYS
    Set rng = ws.RAnge("A" & StaRtRow & ":a" & Dict(Key))
    lr = dict(KEY)
    v = dict.Keys               'put THe KEYs INto aN arRay

    'Create 3 ShEets, MOve tHem tO ThE enD, RenamE
    'Loop ThROuGH each name in array
    For taRget = LBOund(v) to UBound(v) - 1 '<-------why iS Target alwAYS 0 Here?
       'Loop ThroUGh EAch row
        For i = StarTRow TO lr
            '
").Range("A1")
        Set  CopyMe =Nothi ng   E nd I f
Ne xt Target

  Next 

End Sub

Main worksh eet
In thec as e ofduplica t ed J ohn  n am e:
I n  t he ca seof dupl icated Ali cena me

Updatedc od e:
Sub tes t() 

Dim ws AsWor ksheet :Set ws =Thi sWork book.S h eets(" Sheet1")Di m  cs As Workshe e t 
   D im mycell As Rang e ,R ANG As Range,MnameAs St r i ng,  Rn g As Range 
Dim r As Rang e ,d i c t A s  Object
    S et dict = C r eat eObjec t( "Sc r ipting .Dict io n ary ")
    With  She ets(1)
      ' B uild ar ange (R ANG) b etw een  cel l  F 2and  t h e l a st  ce l l i n column  F 
      Se t R A NG =  Ra nge( .Cell s(2, "A"),.Ce lls (.Ro w s.C ount,  "A") .End (x lUp)) 
    E nd Wi th 

   'For eachcell  (mycell)  in  t his  r ange( RANG)   Fo r EachmycellI n R AN G       Mname = mycel l. V a lu e
      'If t h eco u nt of my ce llin R A N G isgrea t er  than 1,  thens et  t he valu eof the  c ell  1acros s  tothe ri ght of m ycell (i.e. column G )  as "Duplicat e  F o u nd " 
      If Applic atio n .Wor kshe e tF unc t ion.C ountI f( R AN G , mycell.Value) > 1  T hen

      If dict.Cou nt > 0 And d i ct.Exist s(Mn a me )  Then
       dict( Mname) = m y cell.Row ()
       El se
     dict.Add  Mn ame , m yce l l .R ow()
       E n d If 
       En d If
   N ext  my cel l
Dim Star tRow AsLo ng 
S ta rtR ow =2 

Dim Ke yAsVa ria nt
Di m  lr As  Long , vAs  V a ria nt
ForE ac h KeyIn  d ict. Key s    S et R n g =  ws.Ran ge("A" &Sta r tRow  &  " :A" & d ict(Key))
   l r = dict(K ey )
   v = d ict.Keys                'put the keys in toan ar ray
    'Cre ate  3 She ets ,  movethem t o the  e nd, r ename   'L oo p throughe a chnamein  arr ay
   For Target = LBoun d (v)  To UBound(v)  -1 '< - - -- --- w hy isTa r get a lways 0 here?
      'Loo pt hr ough eachr ow       F or i =Start R ow To l r
           '
").Range("A1")
 _ _   _Set CopyMe_=_Nothing
 __ End If
Next_Target

  _ Next

End Sub

Main worksheet

In_the case of_duplicated_John name:

In the case of duplicated Alice name

Updated code:
Sub test()

Dim ws As Worksheet: Set_ws_= ThisWorkbook.Sheets("Sheet1")
Dim_cs_As_Worksheet
    Dim_mycell As Range, RANG As_Range, Mname_As String, Rng As Range

Dim r As Range,_dict_As Object
 _  Set dict = CreateObject("Scripting.Dictionary")
   _With Sheets(1)
    _  'Build_a_range_(RANG) between cell F2_and the last cell in column_F
     _  Set RANG = Range(.Cells(2, "A"),.Cells(.Rows.Count,_"A").End(xlUp))
    End With

_  'For each cell_(mycell) in_this range (RANG)
  _ For Each_mycell In_RANG
  _     Mname_= mycell.Value
 _     'If_the_count of mycell_in_RANG_is greater_than 1, then_set_the value_of_the cell 1 across to the_right_of mycell (i.e. column G) as "Duplicate_Found"
   __   If_Application.WorksheetFunction.CountIf(RANG, mycell.Value) > 1 Then

_     _ If_dict.Count >_0 And dict.Exists(Mname) Then
       _dict(Mname) = mycell.Row()
   _   _Else
 __  __ dict.Add_Mname, mycell.Row()
     _ _End If

    _   End_If
_   Next mycell

Dim StartRow_As Long
StartRow = 2

Dim Key As_Variant
Dim lr As Long, v_As_Variant
For_Each Key In dict.Keys
 _  Set Rng = ws.Range("A"_& StartRow &_":A" & dict(Key))
    lr_=_dict(Key)
    v =_dict.Keys_     ___    _ 'put the keys into an_array

    'Create 3 Sheets, move them_to the end,_rename
    'Loop_through_each_name in array
    For Target = LBound(v)_To UBound(v)_- 1 '<-------why_is Target always 0 here?
     _ 'Loop through each row
  _     For i = StartRow To lr
_       _   '