I have successfully written the code to create the work sheet place it correctly in the workbook and rename it, the problem is that during the creation phase the new workbook name is not known and declaring it afterwards seems to create errors. Need to know what I am missing. Work book has template page for creating new sheets, each sheet represents a Job, we need to compile data in an input form - on clicking of the submit button the Vba needs to create the new sheet from template name it and then add the job details to the newly created sheet.

here is my code:

Private Sub BtnSubmit_Click()

Dim Sh As Worksheet, TemplateSh As Worksheet
Dim ShNum As Integer, HighestNum As Integer
Dim SheetCoreName As String
Dim nr As Long
Dim ws As Worksheet
Dim js As Worksheet

Set ws = Worksheets("Summary")
Set js = Worksheets("JobSheet")

nr = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

SheetCoreName = "Sheet"

Set TemplateSh = Sheets("Base")
Set ws = Worksheets("Summary")

For Each Sh In Worksheets
If InStr(1, Sh.Name, SheetCoreName) = 1 Then
ShNum = Val(Right(Sh.Name, Len(Sh.Name) - Len(SheetCoreName)))
If ShNum > HighestNum Then HighestNum = ShNum
End If
Next Sh

TemplateSh.Copy after:=Sheets(Sheets.Count)

ActiveSheet.Visible = xlSheetVisible

ActiveSheet.Name = SheetCoreName & HighestNum + 1

With Summary

ws.Cells(nr, 1).Value = ActiveSheet.Name

End With

nr = Sheets("Summary").Cells(Rows.Count, 3).End(xlUp).Offset(1).Row

ws.Cells(nr, 3).Value = Me.DateIn.Value
ws.Cells(nr, 24).Value = Me.TimeIn.Value
ws.Cells(nr, 8).Value = Me.CustomerPo.Value
ws.Cells(nr, 4).Value = Me.Customer_Name.Value
ws.Cells(nr, 28).Value = Me.Customer_Site.Value
ws.Cells(nr, 5).Value = Me.JobDescription.Value
ws.Cells(nr, 25).Value = Me.Job_Priority.Value
ws.Cells(nr, 9).Value = Me.Origin.Value

With JobSheet

js.Cells(5, 9).Value = Me.DateIn.Value
js.Cells(7, 9).Value = Me.TimeIn.Value
js.Cells(5, 5).Value = Me.Job.Value
js.Cells(5, 1).Value = Me.Customer_Name.Value
js.Cells(17, 5).Value = Me.CustomerPo.Value
js.Cells(17, 9).Value = Me.Job_Priority.Value
js.Cells(9, 5).Value = Me.JobDescription.Value
js.Cells(9, 1).Value = Me.Customer_Site.Value

End With

/ need to now write data to newly created job sheet which will use nr = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)to find the newly created sheet number

End Sub

Any help greatly appreciated.