Sub FS()
'
' FS Macro
'
'
Rows("2:8").Select
Selection.Style = "Normal"
Rows("3:3").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("3:6").Select
Selection.Delete Shift:=xlUp
Rows("2:4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge
Range("E1").Select
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Om"
Rows("7:7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Style = "Normal"
Rows("2:4").Select
Rows("2:4").EntireRow.AutoFit
Cells.Select
With Selection.Font
.Name = "Calibri"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection.Font
.Name = "Calibri"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("A6:J6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 6299648
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("B:B").Select
Range("B6").Activate
Selection.Insert Shift:=xlToRight
Range("B6").Select
ActiveCell.FormulaR1C1 = "Ledger No"
Range("A2:A4").Select
Selection.Cut
Range("B2").Select
ActiveSheet.Paste
Range("B6").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveWorkbook.Save
Columns("B:B").EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 7.43
Columns("C:J").Select
Columns("C:J").EntireColumn.AutoFit
Range("F13").Select
Columns("F:F").ColumnWidth = 21.57
Columns("D:D").Select
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E6").Select
Columns("E:E").ColumnWidth = 15.57
Windows("CC - FY17.xlsb").Activate
Windows("CC Detail by Account 2018.xlsb").Activate
ActiveCell.FormulaR1C1 = "Account Name"
Range("E7").Select
Windows("CC - FY17.xlsb").Activate
Windows("CC Detail by Account 2018.xlsb").Activate
Windows("CC - FY17.xlsb").Activate
Windows("CC Detail by Account 2018.xlsb").Activate
Range("H6").Select
Columns("D:D").ColumnWidth = 14
Windows("CC - FY17.xlsb").Activate
Windows("CC Detail by Account 2018.xlsb").Activate
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H6").Select
ActiveCell.FormulaR1C1 = "Beginning Yr Bal"
Range("H7").Select
Windows("CC - FY17.xlsb").Activate
Range("O12").Select
Windows("CC Detail by Account 2018.xlsb").Activate
Columns("K:K").Select
Range("K7").Activate
Selection.Insert Shift:=xlToRight
Range("K6").Select
ActiveCell.FormulaR1C1 = "Cl Daily Bal"
Range("K7").Select
Windows("CC - FY17.xlsb").Activate
Windows("CC Detail by Account 2018.xlsb").Activate
Columns("L:L").Select
Range("L6").Activate
Selection.Delete Shift:=xlToLeft
Range("L6").Select
ActiveCell.FormulaR1C1 = "Check Daily Bal"
Range("L7").Select
Windows("CC - FY17.xlsb").Activate
Windows("CC Detail by Account 2018.xlsb").Activate
Range("M6").Select
ActiveCell.FormulaR1C1 = "Account Type"
Range("M7").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "FY - GL"
Sheets.Add After:=ActiveSheet
Cells.Select
ActiveWindow.DisplayGridlines = False
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("A3").Select
Windows("CC - FY17.xlsb").Activate
Sheets("FY17 Legend").Select
Range("B7").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Range("B2:C63").Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Previous.Select
Windows("CC Detail by Account 2018.xlsb").Activate
Range("B2").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.ColumnWidth = 8.57
Columns("E:E").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireColumn.Hidden = True
Range("B10").Select
Columns("B:B").EntireColumn.AutoFit
Range("B17").Select
Selection.End(xlDown).Select
Rows("65:65").Select
Range("B65").Activate
Range(Selection, Selection.End(xlDown)).Select
Rows("65:65").Select
Range("B65").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Hidden = True
Range("D64").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("B1").Select
ActiveWorkbook.Save
ActiveSheet.Previous.Select
Range("C8").Select
Windows("CC - FY17.xlsb").Activate
ActiveSheet.Previous.Select
Range("E6").Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""-"",RC[-2])),RC[-1],0)"
Range("E6").Select
Selection.Copy
Windows("CC Detail by Account 2018.xlsb").Activate
Range("E7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""-"",RC[-2])),RC[-1],0)"
Range("C7").Select
Selection.Copy
Range("B7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B8").Select
Windows("CC - FY17.xlsb").Activate
Range("B7").Select
Selection.Copy
Windows("CC Detail by Account 2018.xlsb").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""-"",RC[1])),RC[1],R[-1]C)"
Range("B8").Select
Selection.Copy
Range("C8").Select
Selection.End(xlDown).Select
Range("B10607").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Range("E7").Select
Windows("CC - FY17.xlsb").Activate
Range("E7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("CC Detail by Account 2018.xlsb").Activate
Range("B8").Select
Selection.End(xlDown).Select
Range("E10607").Select
Range(Selection, Selection.End(xlUp)).Select
Range("E8:E10607").Select
Range("E10607").Activate
ActiveSheet.Paste
Selection.End(xlUp).Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("H7").Select
Windows("CC - FY17.xlsb").Activate
Range("H7").Select
Selection.Copy
Windows("CC Detail by Account 2018.xlsb").Activate
Range("H8").Select
ActiveSheet.Paste
Range("H7").Select
ActiveSheet.Paste
Range("H8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("E9").Select
Selection.End(xlDown).Select
Range("H10607").Select
Range(Selection, Selection.End(xlUp)).Select
Range("H7:H10607").Select
Range("H10607").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("CC - FY17.xlsb").Activate
Application.CutCopyMode = False
Windows("CC Detail by Account 2018.xlsb").Activate
Range("B6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("B6:M10607").Select
Selection.AutoFilter
Range("F6").Select
ActiveSheet.Range("$B$6:$M$10610").AutoFilter Field:=5, Criteria1:="="
Range("C6").Select
ActiveSheet.Range("$B$6:$M$10610").AutoFilter Field:=2, Criteria1:="="
Rows("9:9").Select
Range("D9").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Range("C6").Select
ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=2
Range("F6").Select
ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=5
Range("H6").Select
ActiveWorkbook.Save
Range("I8").Select
Windows("CC - FY17.xlsb").Activate
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Windows("CC Detail by Account 2018.xlsb").Activate
Range("K11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F10").Select
Selection.End(xlUp).Select
ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=5, Criteria1:="="
Range("F7").Select
Application.CutCopyMode = False
Range("F6").Select
ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=5
Range("F10").Select
Selection.End(xlUp).Select
Range("F7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("F7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True
Columns("F:F").EntireColumn.AutoFit
Selection.NumberFormat = "[$-en-CA]d/mmm/yy;@"
Range("F8").Select
ActiveWorkbook.Save
Range("F7").Select
ActiveCell.FormulaR1C1 = "1-Sep-2017"
Range("F7").Select
Selection.Copy
Range("F6").Select
ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=5, Criteria1:="="
Range("E8").Select
Selection.End(xlDown).Select
Range("F10059").Select
Range(Selection, Selection.End(xlUp)).Select
Range("F8:F10059").Select
Range("F10059").Activate
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste
Range("F10059").Select
Selection.End(xlUp).Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("F6").Select
ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=5
Range("K7").Select
Windows("CC - FY17.xlsb").Activate
Range("L10").Select
Selection.Copy
Application.CutCopyMode = False
Range("K10").Select
Selection.Copy
Windows("CC Detail by Account 2018.xlsb").Activate
Range("K7").Select
Range("K8").Select
ActiveSheet.Paste
Range("K8").Select
Application.CutCopyMode = False
Selection.Copy
Range("F11").Select
Selection.End(xlDown).Select
Range("K10074").Select
Range(Selection, Selection.End(xlUp)).Select
Range("K8:K10074").Select
Range("K10074").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("K10073").Select
Selection.End(xlUp).Select
Range("L7").Select
Windows("CC - FY17.xlsb").Activate
Range("L5").Select
Selection.Copy
Windows("CC Detail by Account 2018.xlsb").Activate
Application.CutCopyMode = False
Columns("L:L").Select
Range("L6").Activate
Selection.Insert Shift:=xlToRight
Range("L6").Select
ActiveCell.FormulaR1C1 = "Cl Mon Bal"
Range("L8").Select
Windows("CC - FY17.xlsb").Activate
Range("L8").Select
Selection.Copy
Windows("CC Detail by Account 2018.xlsb").Activate
Range("L8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("K8").Select
Selection.End(xlDown).Select
Range("L10074").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Selection.End(xlUp).Select
Range("L11").Select
Selection.End(xlDown).Select
Range("L10073").Select
Selection.End(xlUp).Select
Range("N7").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "FY - Legend"
Sheets("FY - GL").Select
Windows("CC - FY17.xlsb").Activate
Windows("CC Detail by Account 2018.xlsb").Activate
Windows("CC - FY17.xlsb").Activate
Range("N8").Select
Windows("CC Detail by Account 2018.xlsb").Activate
Windows("CC - FY17.xlsb").Activate
Windows("CC Detail by Account 2018.xlsb").Activate
ActiveSheet.Paste
Range("N7").Select
Windows("CC - FY17.xlsb").Activate
Range("N6").Select
Selection.Copy
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("M4").Select
Windows("CC Detail by Account 2018.xlsb").Activate
Range("N4").Select
ActiveSheet.Paste
Range("N7").Select
ActiveCell.FormulaR1C1 = _
"=IF(SUMPRODUCT(NOT(ISERR(SEARCH(RC[-9],'FY - Legend'!R3C2:R63C2)))*1),'FY - Legend'!R2C2,""Balance Sheet"")"
Range("N4").Select
Selection.ClearContents
Range("N7").Select
Selection.Copy
Range("L8").Select
Selection.End(xlDown).Select
Range("N10074").Select
Range(Selection, Selection.End(xlUp)).Select
Range("N7:N10074").Select
Range("N10074").Activate
ActiveSheet.Paste
Selection.End(xlUp).Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Bold = True
Range("B4").Select
ActiveWorkbook.Save
Range("B6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"FY - GL!R6C2:R10074C14", Version:=6).CreatePivotTable TableDestination:= _
"", TableName:="PivotTable1", DefaultVersion:=6
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "FY MoM P&L"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type").CurrentPage _
= "(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type")
.PivotItems("Balance Sheet").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type"). _
EnableMultiplePageItems = True
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
ActiveWindow.DisplayGridlines = False
Range("B1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").AutoGroup
ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarters").Orientation = _
xlHidden
Range("C4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
Range("B4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").PivotItems("2017"). _
ShowDetail = True
Range("F4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").PivotItems("2018"). _
ShowDetail = True
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Cl Mon Bal"), "Sum of Cl Mon Bal", xlSum
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection.Font
.Name = "Calibri"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("B6").Select
Range(Selection, Selection.End(xlDown)).Select
Range("B6:N54").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_-* #,##0.0_-;-* #,##0.0_-;_-* ""-""??_-;_-@_-"
ActiveWindow.ScrollColumn = 1
Columns("B:N").Select
ActiveWorkbook.Save
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D12").Select
ActiveWorkbook.Save
ActiveWindow.SmallScroll Down:=-18
End Sub