Excel Macro to populate the values in the required cells

[Excel Fox]

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

[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.

[Adam991]

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.

Hi Rick Rothstein

I am having problem here ["G9:J" & wks.Cells(Rows.Count, "A")], I was just trying to do it for my practice but it is not giving me results, can you elaborate please?

[pbabu]

Many thanks for your quick reply.
great guys , this is perfectly working. would it be possible to generate a sequence set of values with respect to "P" sheets which means if 1st (P) sheet has a value of "1" then next "P" sheet should contain a value of "2" i,e adding +1 to the next occurrence.

For example :

P Sheet " 1 Minutes -4431 01/01/2013"
S Sheet " 0 Minutes -4431 01/01/2013"
P Sheet " 2 Minutes -4431 01/01/2013"
S Sheet " 0 Minutes -4431 01/01/2013"

Please note there is no change required for "S" sheets.

[Rick Rothstein]

Many thanks for your quick reply.
great guys , this is perfectly working. would it be possible to generate a sequence set of values with respect to "P" sheets which means if 1st (P) sheet has a value of "1" then next "P" sheet should contain a value of "2" i,e adding +1 to the next occurrence.

For example :

P Sheet " 1 Minutes -4431 01/01/2013"
S Sheet " 0 Minutes -4431 01/01/2013"
P Sheet " 2 Minutes -4431 01/01/2013"
S Sheet " 0 Minutes -4431 01/01/2013"

Please note there is no change required for "S" sheets.

Does this code do what you want?

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

[pbabu]

Apologies for the confusion.No Rick, the logic that you have provided is basically generating a sequence set of values with in the same sheet but i need other way around which means complete (P) sheet should have same set of value like ("1 Minutes -4431 01/01/2013")
and next occurence of (P) should have incremental value like (" 2 Minutes -4431 01/01/2013).

For example:
FreeP sheet : " 1 Minutes -4431 01/01/2013".
FreeS Sheet : " 0 Minutes -4431 01/01/2013"
CostP Sheet : "" 2 Minutes -4431 01/01/2013"
CostS Sheet : " 0 Minutes -4431 01/01/2013"

I hope this clears the confusion.

[Rick Rothstein]

Apologies for the confusion.No Rick, the logic that you have provided is basically generating a sequence set of values with in the same sheet but i need other way around which means complete (P) sheet should have same set of value like ("1 Minutes -4431 01/01/2013")
and next occurence of (P) should have incremental value like (" 2 Minutes -4431 01/01/2013).

For example:
FreeP sheet : " 1 Minutes -4431 01/01/2013".
FreeS Sheet : " 0 Minutes -4431 01/01/2013"
CostP Sheet : "" 2 Minutes -4431 01/01/2013"
CostS Sheet : " 0 Minutes -4431 01/01/2013"

Okay then, how about this macro, does it do what you want?

Sub ExcelFoxAndRick()
  Dim Index As Long, Wks As Worksheet, Rng As Range
  For Each Wks In ThisWorkbook.Worksheets
    Set Rng = Wks.Range("G9:J" & Wks.Cells(Rows.Count, "A").End(xlUp).Row)
    If Wks.Name Like "*[PS]" Then Rng = Array(Abs(Wks.Name Like "*P"), "Minutes", -4431, #1/1/2013#)
    If Right(Wks.Name, 1) = "P" Then
      Index = Index + 1
      Rng.Columns(1) = Index
    End If
  Next
End Sub

[pbabu]

it works perfect. Thank you very much rick !