Excel Macro to populate the values in the required cells

**pbabu** · 10-30-2013, 08:55 PM

**patel** · 10-30-2013, 11:30 PM

Code:

Sub a()
For Each sh In Sheets
  LR = sh.Cells(Rows.Count, "A").End(xlUp).Row
  sh.Range("H9:H" & LR) = "Minutes"
  sh.Range("I9:I" & LR) = -4431
  If Right(sh.Name, 1) = "S" Then sh.Range("G9:G" & LR) = 0
  If Right(sh.Name, 1) = "P" Then sh.Range("G9:G" & LR) = 1
Next
End Sub

**Excel Fox** · 10-30-2013, 11:40 PM

Try this from a module or workbook code module

Code:

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** · 11-02-2013, 12:08 PM

Originally Posted by Excel Fox

Try this from a module or workbook code module

Code:

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

Code:

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.

**pbabu** · 11-03-2013, 10:46 PM

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** · 11-04-2013, 12:03 AM

Originally Posted by 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.

Does this code do what you want?

Code:

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** · 11-04-2013, 12:45 PM

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** · 11-04-2013, 07:51 PM

Originally Posted by 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"

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

Code:

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** · 11-04-2013, 11:03 PM

it works perfect. Thank you very much rick !

**pbabu** · 11-20-2013, 11:42 PM

It was very useful rick , would it be possible to avoid hard coding of some values which means rather than hard-coding of "-4431,1/1/2013" can we pick up B1 and B2 values to populate them
for example -4431 can be copied from B1 cell and 1/1/2013 can be copied from B2 Cell.