code not adding sheets

[dpaton05]

My code is not adding a sheet, and it looks like it should. It only has the one sheet called July. What have I done wrong?

Sub AddYP()
Dim NewYP As String
    NewYP = Tracker.Cells(5, 4)
    YP.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = NewYP
    Call CreateWB(NewYP)
End Sub
Sub CreateWB(NewYP As String)
Dim wb1 As Workbook
    Workbooks.Add.SaveAs Filename:=ThisWorkbook.Path & "\Young People\" & NewYP, FileFormat:=52
    With Workbooks(NewYP)
        Sheets(1).Name = "July"
        Sheets.Add '.Name = "August"
    End With
End Sub

 

[Michael M]

With activeworkbook
        Sheets(1).Name = "July"
        Sheets.Add.Name = "August"
end with

 

[Zot]

Once you created a new workbook, the new one is an active workbook. So you just do like this

Sub CreateWB(NewYP As String)
Dim wb1 As Workbook
    Workbooks.Add.SaveAs Filename:=ThisWorkbook.Path & "\Young People\" & NewYP, FileFormat:=52
    Sheets(1).Name = "July"
    Sheets.Add '.Name = "August"
End Sub

I see that you also have unused wb1 declared

 

[Michael M]

And I'm guessing you would want sheet "August" AFTER "July"

With ActiveWorkbook
        Sheets(1).Name = "July"
        Sheets.Add(After:=Sheets("July")).Name = "August"
End With

 

[Norie]

Your With statement isn't really doing anything, try this.

Sub AddYP()
Dim NewYP As String

    NewYP = Tracker.Cells(5, 4)
    YP.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = NewYP
    Call CreateWB(NewYP)
    
End Sub

Sub CreateWB(NewYP As String)
Dim wb1 As Workbook

    Set wb1 = Workbooks.Add
    wb1.SaveAs Filename:=ThisWorkbook.Path & "\Young People\" & NewYP, FileFormat:=52
    With wb1
        .Sheets(1).Name = "July"
        .Sheets.Add.Name = "August"
    End With
    
End Sub

 

[dpaton05]

Thanks for your help guys, I got it to work with this code

Sub CreateWB(NewYP As String)
Dim AddWb As Workbook
    Workbooks.Add.SaveAs Filename:=ThisWorkbook.Path & "\Young People\" & NewYP, FileFormat:=52
    With Workbooks(NewYP)
        .Sheets(1).Name = "July"
        .Sheets.Add.Name = "August"
        .Sheets.Add.Name = "September"
        .Sheets.Add.Name = "October"
        .Sheets.Add.Name = "November"
        .Sheets.Add.Name = "December"
        .Sheets.Add.Name = "January"
        .Sheets.Add.Name = "February"
        .Sheets.Add.Name = "March"
        .Sheets.Add.Name = "April"
        .Sheets.Add.Name = "May"
        .Sheets.Add.Name = "June"
    End With
End Sub

 

[Michael M]

you could have also used

Sub MM1()
    Dim i As Long
    Workbooks.Add.SaveAs Filename:=ThisWorkbook.Path & "\Young People\" & NewYP, FileFormat:=52
    For i = 1 To 12
        With Sheets.Add(, Sheets(i))
            .Name = MonthName(i)
        End With
    Next i
End Sub

 

[dpaton05]

Thanks Michael but would that put the month order in a financial year format, July-June?

 

[Michael M]

Yep....click and drag
Select Months Dec to June and drag them to the fron of the "queue"
BUT my code does....Jan to Dec

 

[dpaton05]

This is the code I have. It creates a sheet1 first then the sheets from January to December.

I want it to be just the sheets July-June

Sub CreateWB(NewYP As String)
Dim i As Long
    If Cells(5, 4) = "" Then
        MsgBox "Please enter a name of the young person you want to add in the name field at D5"
        Exit Sub
    End If
    Call CheckFolderExists
    Workbooks.Add.SaveAs Filename:=ThisWorkbook.Path & "\Young People\" & NewYP, FileFormat:=52
    For i = 1 To 12
        With Sheets.Add(, Sheets(i))
            .Name = MonthName(i)
        End With
    Next i
End Sub