Excel Macro to populate the values in the required cells

[Rick Rothstein]

Try this from a module or workbook code module

Sub ExcelFox()
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        If Right(UCase(wks.Name), 1) = "P" Then
            With wks
                .Range("G9:J" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value = _
                    Array(1, "Minutes", -4431, #1/1/2013#)
            End With
        ElseIf Right(UCase(wks.Name), 1) = "S" Then
            With wks
                .Range("G9:J" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value = _
                    Array(0, "Minutes", -4431, #1/1/2013#)
            End With
        End If
    Next wks
End Sub

You can shorten the above code to this...

Sub ExcelFox()
  Dim wks As Worksheet
  For Each wks In ThisWorkbook.Worksheets
    If wks.Name Like "*[PS]" Then wks.Range("G9:J" & wks.Cells(Rows.Count, "A").End( _
           xlUp).Row) = Array(Abs(wks.Name Like "*P"), "Minutes", -4431, #1/1/2013#)
  Next
End Sub

Note: I wrapped the long line of code using the line continuation character for neatness of display, but you can unwrap that line if you want.