-
1 Attachment(s)
Excel Macro to populate the values in the required cells
-
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
-
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
-
Quote:
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.
-
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.
-
Quote:
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
-
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.
-
Quote:
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
-
it works perfect. Thank you very much rick !
-
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.
-
Quote:
Originally Posted by
Rick Rothstein
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.
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?
