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

 

[Michael M]

Should be

For Each Worksheet In Worksheets

 

[dpaton05]

Thanks Michael.

 

[dpaton05]

My code is only being applied to the June sheet, which is the last sheet. How do I get the code to apply to each sheet?

 

[Michael M]

For Each ws In worksheets
with ws        
        .Range("A1").Value = "Date"
       . Range("A2").Value = "GL Code"
       . Range("A3").Value = "Supplier"
       . Range("A4").Value = "Amount"
       . Range("A5").Value = "Comments"
        With ws.Rows(1)
            .Font.Bold = True
            .Interior.ColorIndex = 15
        End With
       end with 
    Next ws

 

[dpaton05]

That still only put the headers on June

 

[Michael M]

Works fine for me....But are you sure you wanted A1:A5 and not A1:E1
Are you using the code on the correct workbook ??

 

[dpaton05]

I don't know what I did but I copied your code again and used it and this time it worked fine.

Thank you Michael.

 

[dpaton05]

I have this code now and I am trying to get the worksheet name put in A1 of each worksheet but this is only putting the name in June, which is the last sheet

Sub SetupSheets()
Dim ws As Worksheet
    For Each ws In Worksheets
        With ws
            .Range("A3").Value = "Date"
            .Range("B3").Value = "GL Code"
            .Range("C3").Value = "Supplier"
            .Range("D3").Value = "Amount"
            .Range("E3").Value = "Comments"
            With ws.Rows(3)
                 .Font.Bold = True
                 .Interior.ColorIndex = 15
            End With
            With Range("A1")
                .Value = ws.Name
                .Font.Size = 16
            End With
            Range("A:A").ColumnWidth = 15
            Range("B:B").ColumnWidth = 15
            Range("C:C").ColumnWidth = 30
            Range("D:D").ColumnWidth = 15
            Range("E:E").ColumnWidth = 60
        End With
    Next ws
End Sub

 

[Michael M]

Your missing a period before with Range

Sub SetupSheets()
Dim ws As Worksheet
    For Each ws In Worksheets
        With ws
            .Range("A3").Value = "Date"
            .Range("B3").Value = "GL Code"
            .Range("C3").Value = "Supplier"
            .Range("D3").Value = "Amount"
            .Range("E3").Value = "Comments"
            With ws.Rows(3)
                 .Font.Bold = True
                 .Interior.ColorIndex = 15
            End With
            With .Range("A1")
                .Value = ws.Name
                .Font.Size = 16
            End With
            Range("A:A,B:B,D:D").ColumnWidth = 15
            Range("C:C").ColumnWidth = 30
            Range("E:E").ColumnWidth = 60
        End With
    Next ws
End Sub