macros to auto name sheets

It can be done by adding one line. Replace the code in the module to this:

Sub AddSheets()

Dim i As Long
Dim Lr As Long
Dim MySh As Worksheet

Set MySh = ActiveSheet
Lr = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To Lr
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MySh.Range("A" & i).Text
    Range("A1").Value = MySh.Range("A" & i).Value
Next i

End Sub