Multiple If's and Last value in range

Hello everyone!

Can anyone help me out with my issue here?

So far this is my code.

Sub AddRow_CopySheet_Rename2()
    Dim Sheet2 As Variant
    Dim CellX As Range
    Dim WS As Worksheet, WB As Workbook
'-----------------------------
    Dim sShtName As String
    Dim lr As Long
'------------------------------
    If A14 > 100 Then
    Application.ScreenUpdating = False
    Set Sheet2 = Worksheets("Template")
    Sheet2.Visible = True
    Set CellX = ActiveCell
    ActiveWorkbook.Sheets(1).Activate
    Range("Table2").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
'-------------------------------
    lr = Range("A" & Rows.Count).End(xlUp).Row
    sShtName = Range("A" & lr).Value
'-------------------------------
    Range("InsertSection").Select
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("Template")
    WS.Copy After:=Sheets(WB.Sheets.Count)
'-------------------------------
    ActiveSheet.Name = sShtName
'-------------------------------
    Application.Goto CellX
    Sheet2.Visible = False
    Application.ScreenUpdating = True
    ElseIf A14 = 100 Then
     Set Sheet2 = Worksheets("Template2")
    Sheet2.Visible = True
    Set CellX = ActiveCell
    ActiveWorkbook.Sheets(1).Activate
    Range("Table2").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
'-------------------------------
    lr = Range("A" & Rows.Count).End(xlUp).Row
    sShtName = Range("A" & lr).Value
'-------------------------------
    Range("InsertSection").Select
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("Template2")
    WS.Copy After:=Sheets(WB.Sheets.Count)
'-------------------------------
    ActiveSheet.Name = sShtName
'-------------------------------
    Application.Goto CellX
    Sheet2.Visible = False
    Application.ScreenUpdating = True
    Else
    Application.ScreenUpdating = False
'------------------------------
    Set Sheet2 = Worksheets("Template3")
    Sheet2.Visible = True
    Set CellX = ActiveCell
    ActiveWorkbook.Sheets(1).Activate
    Range("Table2").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
'-------------------------------
    lr = Range("A" & Rows.Count).End(xlUp).Row
    sShtName = Range("A" & lr).Value
'-------------------------------
    Range("InsertSection").Select
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("Template3")
    WS.Copy After:=Sheets(WB.Sheets.Count)
'-------------------------------
    ActiveSheet.Name = sShtName
'-------------------------------
    Application.Goto CellX
    Sheet2.Visible = False
    Application.ScreenUpdating = True
    End If
End Sub

Now, what I want is instead of it referring to cell A14, it refers to the last cell with data in it in column A.

How would I go about doing this?

I have since posting this, tried to change some code, and here is what I have come up to.

Option Explicit
Sub AddRow_CopySheet_Rename()
    Dim Sheet2 As Variant
    Dim CellX As Range
    Dim WS As Worksheet, WB As Workbook
'-----------------------------
    Dim sShtName As String
    Dim lr As Long
'------------------------------
    If lr = Range("A" & Rows.Count).End(xlUp).Row < 100 Then
    Application.ScreenUpdating = False
    Set Sheet2 = Worksheets("Template3")
    Sheet2.Visible = True
    Set CellX = ActiveCell
    ActiveWorkbook.Sheets(1).Activate
    Range("Table2").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
'-------------------------------
    lr = Range("A" & Rows.Count).End(xlUp).Row
    sShtName = Range("A" & lr).Value
'-------------------------------
    Range("InsertSection").Select
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("Template3")
    WS.Copy After:=Sheets(WB.Sheets.Count)
'-------------------------------
    ActiveSheet.Name = sShtName
'-------------------------------
    Application.Goto CellX
    Sheet2.Visible = False
    Application.ScreenUpdating = True
    ElseIf lr = Range("A" & Rows.Count).End(xlUp).Row = 100 Then
    Application.ScreenUpdating = False
    Set Sheet2 = Worksheets("Template2")
    Sheet2.Visible = True
    Set CellX = ActiveCell
    ActiveWorkbook.Sheets(1).Activate
    Range("Table2").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
'-------------------------------
    lr = Range("A" & Rows.Count).End(xlUp).Row
    sShtName = Range("A" & lr).Value
'-------------------------------
    Range("InsertSection").Select
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("Template2")
    WS.Copy After:=Sheets(WB.Sheets.Count)
'-------------------------------
    ActiveSheet.Name = sShtName
'-------------------------------
    Application.Goto CellX
    Sheet2.Visible = False
    Application.ScreenUpdating = True
    Else
    Application.ScreenUpdating = False
    Set Sheet2 = Worksheets("Template")
    Sheet2.Visible = True
    Set CellX = ActiveCell
    ActiveWorkbook.Sheets(1).Activate
    Range("Table2").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
'-------------------------------
    lr = Range("A" & Rows.Count).End(xlUp).Row
    sShtName = Range("A" & lr).Value
'-------------------------------
    Range("InsertSection").Select
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("Template")
    WS.Copy After:=Sheets(WB.Sheets.Count)
'-------------------------------
    ActiveSheet.Name = sShtName
'-------------------------------
    Application.Goto CellX
    Sheet2.Visible = False
    Application.ScreenUpdating = True
End If
End Sub

And still it isn't selecting the last value in the range.

PLEASE help me out here guys!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.