hobbiton73
05-07-2014, 08:05 AM
Hi, I wonder whether someone may be able to help me please.
I'm trying to apply formatting to multiple sheets and have put together the following:
Sub MonthlySheetHeaders()
Dim ws As Worksheet
Dim rng As Range
For Each ws In Worksheets(Array("Monthly Direct", "Monthly Enhancements", "Monthly Indirect", "Monhtly Overhead", "Monthly Projects"))
If ws.Name = "Monthly Direct" Then
With Range("B5")
.Value = "Direct Activities summary"
.Offset(2, 0).Resize(, 4).Value = Array("Resource LOB", "Total Forecast FTE", "Total Actuals FTE", "Capacity")
End With
With .Range("G5")
.Value = "Direct Activities Breakdown"
.Offset(2, 0).Resize(, 3).Value = Array("Direct Activity Description", "Resource LOB", "Actuals FTE")
End With
End If
If ws.Name = "Monthly Enhancements" Then
With .Range("B5")
.Value = "Enhancements Breakdown"
.Offset(2, 0).Resize(, 5).Value = Array("Enhancement Description", "Resource LOB", "Forecast FTE", "Actuals FTE", "Capacity")
End With
End If
If ws.Name = "Monthly Indirect" Then
With .Range("B5")
.Value = "Indirect Activites Summary"
.Offset(2, 0).Resize(, 4).Value = Array("Resource LOB", "Total Forecast FTE", "Total Actuals FTE", "Capacity")
End With
With .Range("G5")
.Value = "Indirect Activities Breakdown"
.Offset(2, 0).Resize(, 3).Value = Array("Indirect Activity Description", "Resource LOB", "Actuals FTE")
End With
End If
If ws.Name = "Monthly Overheads" Then
With .Range("B5")
.Value = "Overhead Activites Summary"
.Offset(2, 0).Resize(, 4).Value = Array("Resource LOB", "Total Forecast FTE", "Total Actuals FTE", "Capacity")
End With
With .Range("G5")
.Value = "Overhead Activities Breakdown"
.Offset(2, 0).Resize(, 3).Value = Array("Overhead Activity Description", "Resource LOB", "Actuals FTE")
End With
End If
If ws.Name = "Monthly Projects" Then
With .Range("B5")
.Value = "Projects Breakdown"
.Offset(2, 0).Resize(, 5).Value = Array("Project Name", "Resource LOB", "Forecast FTE", "Actuals FTE", "Capacity")
End With
ws.Columns("B:F").EntireColumn.AutoFit
Next ws
End Sub
I am aware that this is a little long winded but I'm a little unsure, perhaps down to my lack of knowledge, whether there is a better way of writing this. I did try writing separate scripts for each sheets, but again, I thought this was a little unwieldy.
I just wondered whether someone may be able to look at this please and offer some guidance in how I may be able to write this better.
Many thanks and kind regards
Chris
I'm trying to apply formatting to multiple sheets and have put together the following:
Sub MonthlySheetHeaders()
Dim ws As Worksheet
Dim rng As Range
For Each ws In Worksheets(Array("Monthly Direct", "Monthly Enhancements", "Monthly Indirect", "Monhtly Overhead", "Monthly Projects"))
If ws.Name = "Monthly Direct" Then
With Range("B5")
.Value = "Direct Activities summary"
.Offset(2, 0).Resize(, 4).Value = Array("Resource LOB", "Total Forecast FTE", "Total Actuals FTE", "Capacity")
End With
With .Range("G5")
.Value = "Direct Activities Breakdown"
.Offset(2, 0).Resize(, 3).Value = Array("Direct Activity Description", "Resource LOB", "Actuals FTE")
End With
End If
If ws.Name = "Monthly Enhancements" Then
With .Range("B5")
.Value = "Enhancements Breakdown"
.Offset(2, 0).Resize(, 5).Value = Array("Enhancement Description", "Resource LOB", "Forecast FTE", "Actuals FTE", "Capacity")
End With
End If
If ws.Name = "Monthly Indirect" Then
With .Range("B5")
.Value = "Indirect Activites Summary"
.Offset(2, 0).Resize(, 4).Value = Array("Resource LOB", "Total Forecast FTE", "Total Actuals FTE", "Capacity")
End With
With .Range("G5")
.Value = "Indirect Activities Breakdown"
.Offset(2, 0).Resize(, 3).Value = Array("Indirect Activity Description", "Resource LOB", "Actuals FTE")
End With
End If
If ws.Name = "Monthly Overheads" Then
With .Range("B5")
.Value = "Overhead Activites Summary"
.Offset(2, 0).Resize(, 4).Value = Array("Resource LOB", "Total Forecast FTE", "Total Actuals FTE", "Capacity")
End With
With .Range("G5")
.Value = "Overhead Activities Breakdown"
.Offset(2, 0).Resize(, 3).Value = Array("Overhead Activity Description", "Resource LOB", "Actuals FTE")
End With
End If
If ws.Name = "Monthly Projects" Then
With .Range("B5")
.Value = "Projects Breakdown"
.Offset(2, 0).Resize(, 5).Value = Array("Project Name", "Resource LOB", "Forecast FTE", "Actuals FTE", "Capacity")
End With
ws.Columns("B:F").EntireColumn.AutoFit
Next ws
End Sub
I am aware that this is a little long winded but I'm a little unsure, perhaps down to my lack of knowledge, whether there is a better way of writing this. I did try writing separate scripts for each sheets, but again, I thought this was a little unwieldy.
I just wondered whether someone may be able to look at this please and offer some guidance in how I may be able to write this better.
Many thanks and kind regards
Chris