Sub BorrowerFundsEquity() ' ' BorrowerFundsEquity Macro ' ' Range("A1:S1").Select Selection.UnMerge Range("A2:A3").Select Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove Range("B7").Select Selection.Cut Range("A2").Select ActiveSheet.Paste Range("A7").Select Selection.Cut Range("A3").Select ActiveSheet.Paste Range("A1:A4").Select Selection.Cut Range("C1").Select ActiveSheet.Paste Range("A1").Select Cells.Find(What:="Total Families", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate 'rearrange cells at bottom of page 'Borrower Funds Equity Matrix ActiveCell.Range("A1:B3").Select Selection.Cut ActiveCell.Offset(1, 2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(0, 1).Range("A1:A3").Select Selection.Cut ActiveCell.Offset(0, 2).Range("A1").Select ActiveSheet.Paste 'Delete unwanted columns 'Borrower Funds & Equity Matrix Columns("E:E").Select Selection.Delete Shift:=xlToLeft Columns("L:M").Select Selection.Delete Shift:=xlToLeft Range("C1").Select 'Merge cells at bottom of sheet Cells.Find(What:="Total Families", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Range("A1:B3").Select Selection.Merge True With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlTop End With 'Delete left-hand columns Columns("A:B").Select Selection.Delete Shift:=xlToLeft 'Format heading 'Borrower Funds & Equity Matrix Range("A4:B4").Select Selection.Merge With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("A1:M3").Select Selection.Merge True With Selection .HorizontalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext End With Range("A1:M1").Select With Selection.Font .Name = "Calibri" .Size = 16 End With Range("A2:M2").Select With Selection.Font .Name = "Calibri" .Size = 13 .Bold = True End With 'Stack text 'Borrower Funds & Equity Matrix Range("E5").Select ActiveCell.FormulaR1C1 = "Household " & Chr(10) & "Size" Range("F5").Select ActiveCell.FormulaR1C1 = "Annual " & Chr(10) & "Income" Range("G5").Select ActiveCell.FormulaR1C1 = "Adjusted " & Chr(10) & "Income" Range("H5").Select ActiveCell.FormulaR1C1 = "Loan" & Chr(10) & " Limit" Range("K5").Select ActiveCell.FormulaR1C1 = "Total " & Chr(10) & "Funding" Range("L5").Select ActiveCell.FormulaR1C1 = "Appraised " & Chr(10) & "Value" 'Format column width & row height 'Borrower funds equity Columns("A:M").Select Selection.rowHeight = 15 Selection.Columns.AutoFit Rows("1:5").Select Selection.Rows.AutoFit Range("c6").Select If Selection.ColumnWidth > 25 Then Selection.ColumnWidth = 25 End If Range("b6").Select If Selection.ColumnWidth < 10 Then Selection.ColumnWidth = 10 End If If Selection.ColumnWidth > 20 Then Selection.ColumnWidth = 15 End If Range("a6").Select If Selection.ColumnWidth > 30 Then Selection.ColumnWidth = 30 End If Columns("A:M").Select 'page and print setup 'Borrower Funds Equity With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.25) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With With ActiveSheet.PageSetup .PrintTitleRows = "$1:$5" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A:$M" Range("A1:M1").Select 'Borrower Funds Equity End Sub Sub ConstTaskMatrix() ' ' ConstTaskMatrix Macro ' 1/13/14 - 2:06 p.m. ' ' 'Rearrange header area Range("A1:U1").Select Selection.UnMerge Range("A4:C6").Select Selection.UnMerge Range("A1:M1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .MergeCells = False End With Selection.Merge Range("A4:A6").Select Range("A6").Activate Selection.Cut Range("A2").Select Selection.Insert Shift:=xlDown Range("A2:M4").Select Selection.Merge True Range("A3:M3").Select Selection.Cut Range("A2:M2").Select Selection.Insert Shift:=xlDown Rows("6:6").Select Selection.Delete Shift:=xlUp Range("A5:B5").Select Selection.Merge Range("A3:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .MergeCells = True End With Range("A2:M2").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .MergeCells = True End With Range("A1:M1").Select With Selection.Font .Name = "Calibri" .Size = 16 End With Range("A2:M2").Select With Selection.Font .Name = "Calibri" .Size = 13 End With 'insert page breaks before each new grouping 'ConstTaskMatrix Range("A34").Select ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(28, 0).Range("A1").Select If IsEmpty(ActiveCell.Value) Then GoTo 1 End If 'autofit column widths and row heights 'ConstTaskMatrix 1: Columns("A:M").Select Selection.Rows.AutoFit Selection.Columns.AutoFit Selection.Rows.AutoFit 'page and print setup ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "Page &P of &N" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.25) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With With ActiveSheet.PageSetup .PrintTitleRows = "$1:$5" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A:$M" Range("A1:M1").Select 'ConstTaskMatrix End Sub Sub FormatSHARES() ' ' FormatSHARES Macro ' 6/13/2014 1:52 p.m. ' ' Range("A1").Select Selection.Cut Range("A3").Select ActiveSheet.Paste Range("F1").Select On Error GoTo 1 Cells.Find(What:="Grant Extension", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Offset(2, -3).Range("A1").Select Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove ActiveCell.Offset(-2, 3).Range("A1:B1").Select Selection.Cut ActiveCell.Offset(2, -3).Range("A1").Select ActiveSheet.Paste 'SHARES Report 1: Range("A1:E3").Select Selection.Merge True Range("A1:E2").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext End With Range("A1:E1").Select With Selection.Font .Name = "Calibri" .Size = 16 End With Range("A2:E2").Select With Selection.Font .Name = "Calibri" .Size = 12 End With Range("D4").Select 'SHARES Report On Error GoTo 2 Cells.Find(What:="Certified By", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Range("A1:B3").Select Selection.Merge True 'Add percentage ahead/behind Application.Goto Reference:="R4C5" Cells.Find(What:="Planned", After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _ :=True, SearchFormat:=False).Activate ActiveCell.Offset(4, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "=R[-1]C[-1]/R[-1]C-1" ActiveCell.Select Selection.NumberFormat = "0.00%" 2: Range("B5").Select On Error GoTo 3 Cells.Find(What:="Comments By :", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Offset(-1, 0).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(1, 0).Range("A1").Select Selection.ClearContents ActiveCell.Offset(1, 0).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(-2, -4).Range("A1:C1").Select Selection.Merge ActiveCell.Offset(0, 3).Range("A1").Select Cells.Find(What:="Comments By :", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Range("A1:B1").Select Selection.Merge True ActiveCell.Offset(1, 0).Range("A1:D1").Select Selection.Merge True 'SHARES Report 3: Columns("A:E").Select Range("A4").Activate Selection.Rows.AutoFit Selection.Columns.AutoFit Selection.Rows.AutoFit With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With 'Print setup 'SHARES Report ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ActiveSheet.PageSetup.PrintArea = "$A:$E" Range("A1:E1").Select 'SHARES Report End Sub Sub GrantConstRept() ' ' GrantConstRept Macro ' ' 'Rearrange and merge cells in heading Range("A1:L1").Select Selection.UnMerge Range("A1").Select Selection.Cut Range("C1").Select ActiveSheet.Paste Range("B1:B3").Select Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove Range("C4").Select Selection.Cut Range("C1").Select ActiveSheet.Paste Range("B8").Select Selection.Cut Range("C2").Select ActiveSheet.Paste Range("A8").Select Selection.Cut Range("C3").Select ActiveSheet.Paste Range("A5").Select Selection.Cut Range("C5").Select ActiveSheet.Paste Columns("A:B").Select Selection.Delete Shift:=xlToLeft Range("A5:B5").Select Selection.Merge 'format text in heading cells 'grant construction report With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .MergeCells = True End With Range("A1:J3").Select Selection.Merge True With Selection .HorizontalAlignment = xlCenter .WrapText = True End With Range("A1:J1").Select With Selection.Font .Name = "Calibri" .Size = 16 End With Range("A2:J2").Select With Selection.Font .Name = "Calibri" .Size = 13 .Bold = True End With Range("A5:B5").Select Selection.Font.Bold = False 'Stack text in column headers 'grant construction report Range("D6").Select ActiveCell.FormulaR1C1 = "Pledged" & Chr(10) & "Points" Range("E6").Select ActiveCell.FormulaR1C1 = "Construction" & Chr(10) & "Start Date" Range("F6").Select ActiveCell.FormulaR1C1 = "Construction" & Chr(10) & "End Date" Range("G6").Select ActiveCell.FormulaR1C1 = "Months" & Chr(10) & "Elapsed" Range("I6").Select ActiveCell.FormulaR1C1 = "Construction" & Chr(10) & "Points" Range("H6").Select ActiveCell.FormulaR1C1 = "Tasks" & Chr(10) & "Completed" Range("J6").Select ActiveCell.FormulaR1C1 = "Total" & Chr(10) & "EUs" 'Format EUs sum as number 'grant construction report ActiveCell.Offset(0, -1).Range("A1").Select Cells.Find(What:="Total EUs", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Range("A1").Select Selection.NumberFormat = "0.000" 'Format row height and column width Columns("A:J").Select Selection.Rows.AutoFit Selection.Columns.AutoFit Range("A6").Select If Selection.ColumnWidth > 30 Then Selection.ColumnWidth = 30 End If Range("J6").Select If Selection.ColumnWidth > 11 Then Selection.ColumnWidth = 8 End If Columns("A:J").Select 'page and print setup ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.25) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With With ActiveSheet.PageSetup .PrintTitleRows = "$1:$6" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A:$J" Range("A1:J1").Select 'grant construction report End Sub Sub GrantFundsReport() ' ' GrantFundsReport Macro ' 6/13/14 - 1:24 p.m. ' ' Range("A1:G1").Select Selection.UnMerge Range("A1:D5").Select Selection.Merge True Selection.HorizontalAlignment = xlCenter Range("A4:D4").Select Selection.Cut Range("A2:D2").Select ActiveSheet.Paste Cells.Find(What:="Funds Used", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Range("A1:D1").Select With Selection .HorizontalAlignment = xlCenter .MergeCells = True End With ActiveCell.Offset(-1, 0).Range("A1:D2").Select ActiveCell.Activate Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove ActiveCell.Offset(-1, 3).Range("A1:D1").Select Selection.Cut ActiveCell.Offset(1, -3).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(0, 2).Range("A1:B1").Select Selection.Cut ActiveCell.Offset(1, -2).Range("A1").Select ActiveSheet.Paste 'Grant funds report Range("A1:D1").Select With Selection.Font .Name = "Calibri" .Size = 16 End With Range("A2:D2").Select With Selection.Font .Name = "Calibri" .Size = 13 End With Range("A3:D3").Select With Selection.Font .Name = "Calibri" .Size = 12 End With Range("A4").Select Cells.Find(What:="523 Funds Used", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Offset(0, 5).Range("A1").Select Selection.Font.Bold = True ActiveCell.Offset(0, -2).Range("A1:D1").Select Selection.Cut ActiveCell.Offset(1, -3).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Range("A1").Select Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove ActiveCell.Offset(-1, 2).Range("A1:B1").Select Selection.Cut ActiveCell.Offset(1, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(3, 0).Range("A1").Select Selection.EntireRow.Delete ActiveCell.Offset(1, 0).Range("A1").Select Selection.EntireRow.Delete ActiveCell.Offset(1, 0).Range("A1").Select Selection.EntireRow.Delete ActiveCell.Offset(0, 4).Range("A1:B1").Select Selection.Cut ActiveCell.Offset(1, -4).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-2, 2).Range("A1:B1").Select Selection.ClearContents ActiveCell.Offset(1, 0).Range("A1:B1").Select Selection.Cut ActiveCell.Offset(1, -2).Range("A1").Select Selection.Insert Shift:=xlDown ActiveCell.Offset(-2, 4).Range("A1:B1").Select Selection.ClearContents ActiveCell.Offset(-2, 0).Range("A1:B2").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-2, -2).Range("A1:D6").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone 'Grant funds report With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Columns("E:G").Select Selection.Delete Shift:=xlToLeft Columns("A:D").Select Range("A4").Activate Selection.Columns.AutoFit Selection.Rows.AutoFit Selection.Columns.AutoFit Selection.Rows.AutoFit 'Grant Funds Report With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.25) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With With ActiveSheet.PageSetup .PrintTitleRows = "$1:$4" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A:$D" ' Add 523 Funds Available ' Grant Funds Report Range("C4").Select Cells.Find(What:="Actual Cost Per Unit", After:=ActiveCell, LookIn:= _ xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Offset(2, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "523 Funds Available:" With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=R[-5]C[-2]-R[-4]C[-2]-R[-2]C[-2]" Selection.NumberFormat = "$#,##0.00" Range("A1:D1").Select 'Grant Funds Report End Sub Sub LoansFunded502() ' ' Format502LoansFunded Macro ' 1/8 - 11:17 a.m. ' ' Range("A1:P1").Select Selection.UnMerge Range("A1").Select Selection.Cut Range("D1").Select ActiveSheet.Paste Range("A2").Select Selection.Cut Range("D2").Select ActiveSheet.Paste Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove Range("B7").Select Selection.Copy Range("D2").Select ActiveSheet.Paste Range("A7").Select Application.CutCopyMode = False Selection.Copy Range("D3").Select ActiveSheet.Paste Range("D1:K4").Select Application.CutCopyMode = False Selection.Merge True Range("D1:K3").Select With Selection .HorizontalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext End With Range("D1:K1").Select With Selection.Font .Name = "Calibri" .Size = 16 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("D2:K2").Select With Selection.Font .Name = "Calibri" .Size = 13 .Bold = True .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("D3:K3").Select With Selection.Font .Name = "Calibri" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("D4:K4").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("A1").Select ActiveCell.Offset(0, 1).Range("A1").Select On Error GoTo 1: Cells.Find(What:="Totals: ", After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _ :=True, SearchFormat:=False).Activate ActiveCell.Range("A1:A4").Select Selection.Cut ActiveCell.Offset(0, 4).Range("A1").Select ActiveSheet.Paste 1: Range("A1").Select Columns("A:C").Select Selection.Delete Shift:=xlToLeft Range("A5").Select ActiveCell.FormulaR1C1 = "Borrower " & Chr(10) & "Name" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "Loan " & Chr(10) & "Closed On" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Appraised " & Chr(10) & "Value" ActiveCell.Offset(0, 1).Range("A1").Select Columns("A:H").Select Selection.rowHeight = 15 Selection.Columns.AutoFit Rows("1:5").Select Selection.Rows.AutoFit 'page and print setup Columns("A:H").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.25) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With With ActiveSheet.PageSetup .PrintTitleRows = "$1:$6" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A:$H" Range("A1:H1").Select '502 Loans Funded End Sub Sub LotInfoRept() ' ' LotInfoRept Macro ' 1/10/14 - 10:51 a.m. ' ' 'unmerge and rearrange heading info Range("A1:K1").Select Selection.UnMerge Range("A2").Select Selection.Cut Range("B5").Select ActiveSheet.Paste Range("A1").Select Selection.Cut Range("C1").Select ActiveSheet.Paste Range("C5").Select Selection.Cut Range("C2").Select ActiveSheet.Paste Range("C4").Select Selection.Cut Range("C3").Select ActiveSheet.Paste Range("B5:C5").Select Selection.Merge With Selection .HorizontalAlignment = xlLeft .MergeCells = True End With 'merge across top cells 'lot info report Range("B1:H3").Select Selection.Merge True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom End With 'format text on heading Range("B1:H1").Select With Selection.Font .Name = "Calibri" .Size = 16 End With Range("B2:H2").Select With Selection.Font .Name = "Calibri" .Size = 13 End With Range("B3:H3").Select Selection.Font.Bold = False Range("B5:C5").Select Selection.Font.Bold = False ActiveCell.Offset(0, -1).Range("A1").Select 'rearrange bottom page totals 'lot info report Cells.Find(What:="Total Number of Lots", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Offset(0, 2).Range("A1:B1").Select Selection.Cut ActiveCell.Offset(1, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 4).Range("A1:B1").Select Selection.Cut ActiveCell.Offset(2, -4).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-2, 0).Range("A1:B3").Select Selection.Cut ActiveCell.Offset(0, 2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(0, -1).Range("a1:b3").Select Selection.Merge True With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom End With 'Deleting columns of useless information 'lot info report Columns("A:A").Select Selection.Delete Shift:=xlToLeft Columns("H:I").Select Selection.Delete Shift:=xlToLeft Range("D6").Select 'stacking text ActiveCell.FormulaR1C1 = "Borrower " & Chr(10) & "Assigned" Range("E6").Select ActiveCell.FormulaR1C1 = "Lot " & Chr(10) & "Area" Range("G6").Select ActiveCell.FormulaR1C1 = "Development " & Chr(10) & "Fees" 'formatting column widths and row heights Columns("A:G").Select Selection.rowHeight = 15 Selection.Columns.AutoFit Range("A1:G5").Select Selection.Rows.AutoFit Rows("6:6").Select Selection.rowHeight = 30 Range("A4").Select If Selection.ColumnWidth > 11 Then Selection.ColumnWidth = 11 End If Range("B4").Select If Selection.ColumnWidth > 40 Then Selection.ColumnWidth = 40 End If If Selection.ColumnWidth < 16.5 Then Selection.ColumnWidth = 16.5 End If Range("C4").Select If Selection.ColumnWidth > 40 Then Selection.ColumnWidth = 40 End If Columns("A:G").Select 'page and print setup 'Lot Info Report With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.25) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With With ActiveSheet.PageSetup .PrintTitleRows = "$1:$6" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A:$G" Range("A1:G1").Select 'lot info report End Sub