Try this 
Option Explicit
Const StartSheet = 2 ' Starting Sheet
Dim SheetNo As Integer

Sub Insert()
Dim CE As String ' from user
Dim CEName As String ' from user
Dim lrow As long
CE = InputBox("Enter SIS GROUP REF", "String Input", "")
CEName = InputBox("Enter SIS GROUP NAME", "String Input", "")
lrow=worksheets("Cover Sht.").range("G" & rows.count).end(xlup).row
Worksheets("Cover Sht.").Range("G" & lrow+1) = CE
Worksheets("Cover Sht.").Range("H" & lrow+1) = CEName
Worksheets("Sample").Copy After:=Sheets(3)
Worksheets("Sample (2)").Select
Worksheets("Sample (2)").Name = CE
Worksheets(CE).Range("AQ32") = CE
Worksheets(CE).Range("AQ33") = CEName
Worksheets(CE).Range("AS37") = SheetNo
End Sub
Does this work?