Sub Month_Updates()
'
' Month_Updates Macro
' Formatting of boardpack for each month for each department
'
' Keyboard Shortcut: Ctrl+w
'
Range("A9:N9").Select
ActiveCell.FormulaR1C1 = _
" CORPORATE FINANCIAL REPORTS TO THE 30th November 2013"
Range("B43").Select
ActiveCell.FormulaR1C1 = "8) SOLVENCY STATEMENT AS AT THE 3OTH NOVEMBER 2013"
ActiveSheet.Next.Select
Range("K5:K56").Select
Selection.Copy
Range("M5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Q5:Q12,Q18:Q23,Q31:Q35,Q41,Q49:Q54").Select
Range("Q49").Activate
Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("H5:H12,H18:H23,H31:H35,H41,H49:H54").Select
Range("H49").Activate
Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Cons Profit & Loss").Select
Columns("B:N").Select
Selection.EntireColumn.Hidden = False
Columns("G:M").Select
Selection.EntireColumn.Hidden = True
Columns("E:E").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("N4").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Columns("W:W").Select
Selection.EntireColumn.Hidden = True
Range("D9").Select
Selection.AutoFill Destination:=Range("d9:e9"), Type:=xlFillDefault
Range("E9:F9").Select
Range("E9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D7:G15").Select
Range("F9").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434828
.TintAndShade = 0
.PatternTintAndShade = 0
Range("E41:E43").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("d41:e43"), Type:=xlFillDefault
Range("E41:F43").Select
Range("E41:E43").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F41:F43").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434828
.TintAndShade = 0
.PatternTintAndShade = 0
Sheets("Aust Summary").Select
Range("E8:E13,E16:E19,E23,E27:E30").Select
Range("E27").Activate
Selection.Replace What:="H", Replacement:="I", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Detail Aust Opers Results").Select
Columns("E:Q").Select
Selection.EntireColumn.Hidden = False
Columns("J:P").Select
Selection.EntireColumn.Hidden = True
Columns("H:H").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("Q7").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("Q11").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Range("Q17:Q378").Select
Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("R17:R378").Select
Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("R17").Select
Range("H366").Select
Selection.AutoFill Destination:=Range("H366:I366"), Type:=xlFillDefault
Range("H366:I366").Select
Range("H366").Select
Selection.Copy
ActiveSheet.Paste
Sheets("Detail Aust Dept Results").Select
Columns("E:Q").Select
Selection.EntireColumn.Hidden = False
Columns("J:P").Select
Selection.EntireColumn.Hidden = True
Columns("H:H").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("Q10").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Columns("E:Q").Select
Selection.EntireColumn.Hidden = False
Columns("J:P").Select
Selection.EntireColumn.Hidden = True
Columns("H:H").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("Q8").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("Q12").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Range("Q17:Q175").Select
Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("R17:R177").Select
Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.Next.Select
Columns("E:Q").Select
Selection.EntireColumn.Hidden = False
Range("I17:I161").Select
Selection.Copy
Range("I17").Select
ActiveSheet.Paste
Range("I17:I161").Select
Application.CutCopyMode = False
Selection.Copy
Range("H17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("J:P").Select
Selection.EntireColumn.Hidden = True
Columns("AH:AK").Select
Selection.EntireColumn.Hidden = False
Range("AJ17:AJ161").Select
Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Columns("H:H").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("Q8").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("Q11").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Range("Q16:Q160").Select
Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("R16:R160").Select
Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Columns("C:O").Select
Selection.EntireColumn.Hidden = False
Range("F15:F161").Select
Selection.Copy
Range("G15").Select
ActiveSheet.Paste
Range("F15:F161").Select
Application.CutCopyMode = False
Selection.Copy
Range("F15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("H:N").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("O7").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Columns("X:X").Select
Selection.EntireColumn.Hidden = True
Range("O15:O139").Select
Selection.Replace What:="X", Replacement:="Y", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("P15:P139").Select
Selection.Replace What:="F", Replacement:="G", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("Q15").Select
ActiveSheet.Next.Select
Columns("E:Q").Select
Selection.EntireColumn.Hidden = False
Columns("J:P").Select
Selection.EntireColumn.Hidden = True
Columns("H:H").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("Q8").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("Q11").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Range("Q15:Q115").Select
Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("R15:R115").Select
Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.Next.Select
Columns("E:Q").Select
Selection.EntireColumn.Hidden = False
Columns("J:P").Select
Selection.EntireColumn.Hidden = True
Columns("H:H").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("Q8").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("Q11").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Range("Q15:Q115").Select
Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("R15:R115").Select
Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.Next.Select
Columns("E:Q").Select
Selection.EntireColumn.Hidden = False
Columns("J:P").Select
Selection.EntireColumn.Hidden = True
Columns("H:H").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Range("Q7").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("Q10").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("Q16:Q141").Select
Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("R16:R141").Select
Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.Next.Select
Columns("E:Q").Select
Selection.EntireColumn.Hidden = False
Columns("J:P").Select
Selection.EntireColumn.Hidden = True
Columns("H:H").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Range("Q8").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("Q11").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("Q16:Q141").Select
Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("R16:R141").Select
Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Bookmarks