PDA

View Full Version : [SOLVED] VBA Multiple Sheet Formatting



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

Bob Phillips
05-07-2014, 08:29 AM
I would just ditch the loop


Sub MonthlySheetHeaders()

With Worksheets("Monthly Direct")

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 With

With Worksheets("Monthly Enhancements")

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 With

With Worksheets("Monthly Indirect")

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 With

With Worksheets("Monthly Overheads")

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 With

With Worksheets("Monthly Projects")

With .Range("B5")
.Value = "Projects Breakdown"
.Offset(2, 0).Resize(, 5).Value = Array("Project Name", "Resource LOB", "Forecast FTE", "Actuals FTE", "Capacity")
End With
.Columns("B:F").EntireColumn.AutoFit
End With
End Sub

Bob Phillips
05-07-2014, 08:37 AM
You can reduce it a bit more


Sub MonthlySheetHeaders()

With Worksheets("Monthly Direct")

With .Range("B5,G5")
.Value = Array("Direct Activities summary", "Direct Activities Breakdown")
.Cells(1, 1).Offset(2, 0).Resize(, 4).Value = Array("Resource LOB", "Total Forecast FTE", "Total Actuals FTE", "Capacity")
.Cells(1, 1).Offset(2, 5).Resize(, 3).Value = Array("Direct Activity Description", "Resource LOB", "Actuals FTE")
End With
End With

With Worksheets("Monthly Enhancements")

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 With

With Worksheets("Monthly Indirect")

With .Range("B5,G5")
.Value = Array("Indirect Activites Summary", "Indirect Activities Breakdown")
.Cells(1, 1).Offset(2, 0).Resize(, 4).Value = Array("Resource LOB", "Total Forecast FTE", "Total Actuals FTE", "Capacity")
.Cells(1, 1).Offset(2, 5).Resize(, 3).Value = Array("Indirect Activity Description", "Resource LOB", "Actuals FTE")
End With
End With

With Worksheets("Monthly Overheads")

With .Range("B5,G5")
.Value = Array("Overhead Activites Summary", "Overhead Activities Breakdown")
.Cells(1, 1).Offset(2, 0).Resize(, 4).Value = Array("Resource LOB", "Total Forecast FTE", "Total Actuals FTE", "Capacity")
.Cells(1, 1).Offset(2, 5).Resize(, 3).Value = Array("Overhead Activity Description", "Resource LOB", "Actuals FTE")
End With
End With

With Worksheets("Monthly Projects")

With .Range("B5")
.Value = "Projects Breakdown"
.Offset(2, 0).Resize(, 5).Value = Array("Project Name", "Resource LOB", "Forecast FTE", "Actuals FTE", "Capacity")
End With
.Columns("B:F").EntireColumn.AutoFit
End With
End Sub

hobbiton73
05-07-2014, 08:53 AM
Hi @xld, thank you very much for taking the time to reply to my post and for the guidance and code, it's exactly what I was after.

All the best and kind regards

Chris

snb
05-07-2014, 01:37 PM
or


Sub M_snb()
With Sheets("Monthly Direct").Range("B5")
.resize(,5)=array("Direct Activities summary","","","","","Direct Activities Breakdown")
.Offset(2).Resize(, 4) = Array("Resource LOB", "Total Forecast FTE", "Total Actuals FTE", "Capacity")
.Offset(2, 5).Resize(, 3) = Array("Direct Activity Description", "Resource LOB", "Actuals FTE")
End With

With Sheets(Array("monthly direct", "monthly indirect", "monthly overheads"))
.FillAcrossSheets Worksheets("Monthly Direct").Range("B5").Offset(2).Resize(, 4)
.FillAcrossSheets Worksheets("Monthly Direct").Range("B5").Offset(2, 5).Resize(, 3)
End With
Sheets("Monthly Indirect").Range("B5:G5") = Array("Indirect Activities Summary", "", "", "", "", "Indirect Activities Breakdown")
Sheets("Monthly Overheads").Range("B5:G5") = Array("Overhead Activities Summary", "", "", "", "", "Overhead Activities Breakdown")

With Sheets("Monthly Enhancements").Range("B5")
.Value = "Enhancements Breakdown"
.Offset(2).Resize(, 5).Value = Array("Enhancement Description", "Resource LOB", "Forecast FTE", "Actuals FTE", "Capacity")
End With
Sheets(Array("Monthly enhancements", "Monthly projects")).FillAcrossSheets Sheets("Monthly enhancements").Range("B5").Offset(2).Resize(, 5)

Sheets("Monthly Projects").Range("B5:B7") = Application.Transpose(Array("Projects Breakdown", "", "Project Name"))
End Sub

hobbiton73
05-08-2014, 08:05 AM
Hi @snb, thank you very much for taking the time to reply to my post and for the alternate code, very interesting.

All the best and kind regards

Chris