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

 

[jolivanes]

You're missing periods all over the place.

 

[Michael M]

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

 

[dpaton05]

Thanks, I totally missed that period.

 

[dpaton05]

You're missing periods all over the place.

Where as it looks ok to me?

 

[dpaton05]

I think I just found them.

 

[Michael M]

Post #12 should be correct

 

[dpaton05]

Thanks Michael. I now need to add a total cell. What have I done wrong with my code as it says there is a syntax error with my formula to add column D from D4 to the bottom.

Sub SetupSheets()
Dim ws As Worksheet, LastRow As Long
    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
            With .Range("I10")
                .Value = "Monthly Total"
                .Font.Bold = True
                .Font.Size = 16
            End With
            LastRow = .Range("F4").End(xlUp).Row
            .Range("I11").formula="=sum(D4:D & Lastrow & ")"
        End With
    Next ws
End Sub

 

[dpaton05]

I worked it out, I was missing a "

 

[Michael M]

missing a double quote

 

[dpaton05]

But I haven't defined sht.