Formatting each sheet in new workbooks with vba code

[dpaton05]

I have code that creates a workbook if it doesn't exist. I then want to format the worksheets but with my code I get the error, object doesn't support this property or method. The line that is highlighted is in SetupSheets:

For Each Worksheet In ThisWorkbook

Sub AddYP()
Application.DisplayAlerts = False
Dim newyp As String
    newyp = Tracker.Cells(5, 4)
    YP.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
        Call CreateWB(newyp)
    ThisWorkbook.Names.Add Name:="tblYPNames", _
    RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
        Tracker.cboYP.ListFillRange = "tblYPNames"
        Tracker.cboDeleteYP.ListFillRange = "tblYPNames"
Application.DisplayAlerts = True
End Sub


Sub CreateWB(newyp As String)
Dim V
        CheckFolderExists
        Workbooks.Add.SaveAs ThisWorkbook.Path & "\Young People\" & newyp, 52
    For Each V In Split("7 8 9 10 11 12 1 2 3 4 5 6")
        Sheets.Add(, Sheets(Sheets.Count)).Name = MonthName(V)
    Next
    Call SetupSheets
    Sheets("sheet1").Delete
ActiveWorkbook.Close savechanges:=True
Tracker.Cells(5, 4).Clear
End Sub

Sub SetupSheets()
Dim Worksheet As Worksheet
    For Each Worksheet In ThisWorkbook
        Range("A1").Value = "Date"
        Range("A2").Value = "GL Code"
        Range("A3").Value = "Supplier"
        Range("A4").Value = "Amount"
        Range("A5").Value = "Comments"
        With Rows(1)
            .Font.Bold = True
            .Interior.ColorIndex = 15
        End With
        
    Next
End Sub

 

[dpaton05]

Thanks Michael but there are still things wrong with it. I have to go now but I will continue when I am at work next.

 

[jolivanes]

A slightly different approach

Sub SetupSheets()
Dim j As Long, colWidth, i As Long
colWidth = Array(15, 15, 30, 15, 60)
Application.ScreenUpdating = False
    For j = 1 To ThisWorkbook.Worksheets.Count
        With Sheets(j)
        With .Range("A1")
            .Value = Worksheets(j).Name
            .Font.Size = 16
        End With
        With .Range("A3")
            .Resize(, 5).Value = Array("Date", "GL Code", "Supplier", "Amount", "Comments")
                With .Resize(, Worksheets(j).UsedRange.Columns.Count)
                    .Font.Bold = True
                    .Interior.ColorIndex = 15
                End With
        End With
        For i = 1 To 5
            .Columns(i).ColumnWidth = colWidth(i - 1)
        Next i
        End With
    Next j
Application.ScreenUpdating = True
End Sub

 

[dpaton05]

A slightly different approach

Thanks for the different way to could write it jolivanes. I think I might stick to something I understand as it makes it easier to debug.