Cfrgtkky

Hi i am am trying to add formulas to specific ranges on each worksheet in my workbook, however it only works on the last sheet in the workbook rather all of the worksheets in the workbook

Sourcesheet is the sheet from which the data for individual schools are copied to a new worksheet

Sub Formatting()

     Dim sourceSheet As Worksheet

    Dim lrow As Long



Set sourceSheet = ThisWorkbook.Worksheets(Sheets.Count)



With sourceSheet

lrow = .Cells(rcell, Col_Western).End(xlUp).Row

End With





    For rcell = 1 To lrow

    CharacterIndex = InStr(1, sourceSheet.Cells(rcell, Col_Western), "Delivery for Creative", vbBinaryCompare)

    If CharacterIndex > 0 Then

    deliveryname = "CS"



With ThisWorkbook.Worksheets.add

.Name = deliveryname

sourceSheet.Range(sourceSheet.Cells(rcell, Col_Western), sourceSheet.Cells(lastrow, Col_phone).End(xlDown)).Copy .Range("A1")

Cells.Select

Selection.RowHeight = 50

Selection.ColumnWidth = 30

'Add Autofilter to Row above student details

Range("a8:e8").EntireRow.AutoFilter

End With

End If



Next rcell







For Each Grey_ws In ThisWorkbook.Worksheets

Call Grey_VALUE_AND_RANGE_ALL(Grey_ws)

Next Grey_ws



End Sub



Sub Grey_VALUE_AND_RANGE_ALL(Grey_ws As Worksheet)

    With Grey_ws

        .Range("A5").FormulaR1C1 = "=Count_items_SmallWest()"

        .Range("A6").FormulaR1C1 = "=Count_items_LargeWest()"

        .Range("B5").FormulaR1C1 = "=Count_items_Small_Asian()"

        .Range("B6").FormulaR1C1 = "=Count_items_Large_Asian()"

        .Range("C5").FormulaR1C1 = "=Count_items_Small_Veg()"

        .Range("C6").FormulaR1C1 = "=Count_items_Large_Veg()"

        .Range("D5:D6").FormulaR1C1 = "=Count_items_Salad()"

        .Range("E5:E6").FormulaR1C1 = "=Count_items_Dessert()"

        .Range("F5:F6").FormulaR1C1 = "=Count_items_Snack()"

    End With

End Sub