All I am trying to add a new worksheet and name it what ever the cell value
is from my main sheet. The name will always be a number plus one. Example
in my "Inputsheet' in column BA row 9 is a number 1701, so with what I am
trying to do is ref BA9 and copy it to BA10 plus one (cell BA10 then should
say 1702).

I have attached my code which is not working (got part of this code from Ron
de Bruin web site):

Private Sub CreateNewSheet1_Click()
Dim LastRow As Long
Dim rng As Range

With ActiveSheet
LastRow = .Cells(.Rows.Count, "BA").End(xlUp).Row
With rng.Parent
.Select
.Range(LastRow).Select
End With
LastRow = ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Selection.Copy
ActiveSheet.Range(LastRow).Select
ActiveSheet.Paste

End With

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = LastRow

ActiveSheet.Range("A1").Select

End Sub