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.
Bookmarks