Option Explicit

Dim sOutFile$, sTitle1$, sTitle2$, sTitle3$, sTitle4$, sAddr1$, sCsz$
Dim bOutFile As Boolean
Dim bPrint As Boolean

Sub Auto_Open()

  Call CheckFlag
  
  If bOutFile Then
    Application.Visible = False
    Application.ScreenUpdating = False
  End If
  
  Application.DisplayAlerts = False
  
  Call Coll_Summary
  Call TitleFormat
  Call SheetFormat
  Call Add_Logo
  

  If bOutFile Then
    Call OutFile
  End If
  
  Windows("ro_Collection_Status_Summary.xls").Activate
  If bOutFile Then
    ActiveWorkbook.Close ( 0 )
  ElseIf bPrint Then
    Application.Visible = True
    Application.ScreenUpdating = True
    Windows("ro_Collection_Status_Summary.xls").WindowState = xlMaximized
    ActiveWindow.SelectedSheets.PrintOut Copies:= 1 , Collate:=True
    Application.Quit

  Else
    Application.ScreenUpdating = True
    Windows("ro_Collection_Status_Summary.XLS").WindowState = xlMaximized
    Cells( 1 ,  1 ).Select
  End If
  

End Sub

Sub Coll_Summary()
  
  Dim iRows%, iRowX%, iRowY%
  Dim i%, j%, ij%, iLength%, ii%, iStatus%
  Dim sNote$
  Dim dShares As Double
  Dim sChargeDesc$, sChargeGroup$, sTotal$
  Dim dGrossReceipts As Double
  Dim dNSFReceipts As Double
  Dim dTotalReceipts As Double


  Windows("ro_Collection_Status_Summary.XLS").WindowState = xlMinimized
  Windows("ro_Collection_Status_Summary.CSV").WindowState = xlMinimized
  Application.ScreenUpdating = False

  Workbooks("ro_Collection_Status_Summary.xls").Worksheets("Sheet1").Activate
  Cells.Select
  Selection.Delete
  
  'delete all objects
  Range("A1:AT" &  55 ).Delete
  
  Workbooks("ro_Collection_Status_Summary.csv").Activate
  sOutFile = Workbooks("ro_Collection_Status_Summary.csv").Worksheets( 1 ).Cells( 1 ,  1 )
  iRows = ActiveCell.SpecialCells(xlLastCell).Row

'copy & paste csv into xls
  Workbooks("ro_Collection_Status_Summary.csv").Activate
  Range("A1:L" & iRows).Select
  Range("A1:L" & iRows).Copy
  Workbooks("ro_Collection_Status_Summary.xls").Activate
  Range("A1:L" & iRows).Select
  ActiveSheet.Paste

'Close csv
  Windows("ro_Collection_Status_Summary.csv").Activate
  ActiveWorkbook.Close ( 0 )

'get address and CityStateZip
  For i =  1  To  6 
    If Left(Cells(i,  1 ),  4 ) = "ADD1" Then
      iLength = InStr(Cells(i,  1 ), "CSZ=")
      sAddr1 = Mid(Cells(i,  1 ),  7 , iLength -  7 )
      sCsz = Mid(Cells(i,  1 ), iLength +  5 )
      Rows(CStr(i) + ":" + CStr(i)).Select
        Selection.Delete Shift:=xlUp
      Exit For
    End If
  Next i
  
    'Store 3 titles and delete section
  sTitle2 = Cells( 2 ,  1 ).Value
  sTitle1 = Cells( 3 ,  1 ).Value
  sTitle3 = Cells( 4 ,  1 ).Value
  sTitle4 = Cells( 5 ,  1 ).Value
  
  Rows("1:5").Select        'delete title rows
    Selection.Delete Shift:=xlUp
    
  Rows("4:5").Select        'insert blank lines after headings
    Selection.Insert Shift:=xlDown


'If Mid(Cells(4, 1), 1, 11) = "Grand Total" Then
'  ii = 2
'Else
'  ii = 8
'End If

'set columns
  Columns("A:A").ColumnWidth =  10 
  Columns("B:B").ColumnWidth =  22 #
  Columns("C:C").ColumnWidth =  10 . 14 
  Columns("D:E").ColumnWidth =  8 . 25 
  Columns("F:F").ColumnWidth =  1 . 8 
  Columns("G:G").ColumnWidth =  8 . 25 
  Columns("H:H").ColumnWidth =  1 . 8 
  Columns("I:I").ColumnWidth =  8 . 25 
  Columns("J:J").ColumnWidth =  1 . 8 
  Columns("K:L").ColumnWidth =  8 . 25 
  Columns("M:M").ColumnWidth =  20 
 
  Columns("B:B").InsertIndent  2    'INDENT CHARGE NAMES
  Columns("D:L").NumberFormat = "#,##0.00_);(#,##0.00)"

ii =  5 
While Mid(Cells(ii,  1 ),  1 ,  11 ) <> "Grand Total"

  'right justify the building#
  If UCase(Mid(Trim(Cells(ii,  1 )),  1 ,  8 )) = "BUILDING" Then
    Cells(ii,  1 ).Select
    Selection.HorizontalAlignment = xlRight
  End If
  
  'if col(1) is a group #, then clear the cell - based on ZZ for charge desc
  If UCase(Mid(Trim(Cells(ii,  2 )),  1 ,  2 )) = "ZZ" Then
    Cells(ii,  1 ).Select
      Selection.ClearContents
    sChargeDesc = Mid(Trim(Cells(ii,  2 )),  3 )
    Cells(ii,  2 ) = sChargeDesc
    Cells(ii,  2 ).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .IndentLevel =  2 
        .ShrinkToFit = False
        .MergeCells = False
    End With
  End If

  If UCase(Mid(Trim(Cells(ii,  1 )),  1 ,  2 )) = "XX" Then
    sChargeGroup = Mid(Trim(Cells(ii,  1 )),  3 )
    Cells(ii,  2 ) = sChargeGroup
    Cells(ii,  2 ).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation =  0 
        .IndentLevel =  0 
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Cells(ii,  1 ).Select
      Selection.ClearContents
  End If
  
  'total lines to be displayed
  If UCase(Mid(Trim(Cells(ii,  1 )),  1 ,  5 )) = "TOTAL" Then
  
       'building section totals
      'If UCase(Mid(Trim(Cells(ii, 1)), 1, 8)) <> "TOTAL XX" Then
        sTotal = UCase(Trim(Cells(ii,  1 )))
        Cells(ii,  2 ) = sTotal
        Cells(ii,  2 ).Select
          Selection.HorizontalAlignment = xlLeft
          Selection.InsertIndent - 2 
      'End If
      
      Cells(ii,  1 ).Select
      Selection.ClearContents
        'over line
      Range("D" & ii & ":E" & ii).Select
      With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
      End With
      Range("G" & ii & ":G" & ii).Select
      With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
      End With
      Range("I" & ii & ":I" & ii).Select
      With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
      End With
      Range("K" & ii & ":L" & ii).Select
      With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
      End With
      
      If UCase(Mid(Trim(Cells(ii,  2 )),  1 ,  14 )) = "TOTAL BUILDING" Or _
         UCase(Mid(Trim(Cells(ii,  2 )),  1 ,  14 )) = "TOTAL PROPERTY" Then
        'bottom underline too
        Range("D" & ii & ":E" & ii).Select
        With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
        End With
        Range("G" & ii & ":G" & ii).Select
        With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
        End With
        Range("I" & ii & ":I" & ii).Select
        With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
        End With
        Range("K" & ii & ":L" & ii).Select
        With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
        End With
        
        'add page break at the next row, col(1)
        If UCase(Mid(Trim(Cells(ii,  2 )),  1 ,  14 )) <> "TOTAL PROPERTY" Then
          Range("A" & ii +  1 ).Select
          ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        End If
        
      End If     'building total
    End If      'totals general
 
    If UCase(Mid(Trim(Cells(ii,  2 )),  1 ,  8 )) = "TOTAL XX" And _
      Cells(ii -  2 ,  3 ) = "" Then
      Rows(CStr(ii) + ":" + CStr(ii)).Select
      Selection.Delete Shift:=xlUp
    Else
      If UCase(Mid(Trim(Cells(ii,  2 )),  1 ,  8 )) = "TOTAL XX" And _
                        Cells(ii -  2 ,  3 ) <> "" Then
      Cells(ii,  2 ) = ""
      End If
    End If
      
    If UCase(Mid(Trim(Cells(ii,  1 )),  1 ,  16 )) = "PAYMENT ANALYSIS" Then
       'NSF section
      dGrossReceipts = Cells(ii,  4 )
      dNSFReceipts = Cells(ii,  5 )
      dTotalReceipts = Cells(ii,  9 )
      'add 3 rows above to separate from property summary
      Rows(CStr(ii -  1 ) + ":" + CStr(ii +  1 )).Select
        Selection.Insert Shift:=xlDown
      ii = ii +  3 

      'insert 6 lines
      Rows(CStr(ii +  1 ) + ":" + CStr(ii +  6 )).Select
        Selection.Insert Shift:=xlDown

      'clear first line, add labels 'TOTALS' in col(9), double lines
      Range("A" & ii & ":L" & ii).Select
      Selection.ClearContents
      Cells(ii,  2 ) = "PAYMENTS ANALYSIS"
      Cells(ii,  9 ) = "TOTAL"
      Range("I" & ii & ":I" & ii).Select
      With Selection
        .HorizontalAlignment = xlRight
      End With
      
      Range("B" & ii & ":I" & ii).Select
        With Selection.Borders(xlEdgeTop)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
        End With
      Range("B" & ii & ":I" & ii).Select
        With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
        End With
        
      'put Gross receipts cell ii + 2, 9, 'PAYMENTS'
      Cells(ii +  2 ,  9 ) = dGrossReceipts
      Cells(ii +  2 ,  2 ) = "PAYMENTS"
      
      'put NSF cells(ii + 3, 9) 'NSF RETURNS'
      Cells(ii +  3 ,  9 ) = dNSFReceipts
      Cells(ii +  3 ,  2 ) = "NSF RETURNS"
      
      Range("B" & ii +  2  & ":B" & ii +  3 ).Select
      With Selection
        .HorizontalAlignment = xlLeft
        .IndentLevel =  4 
      End With


      'put total (ii + 5, 9), double lines 'TOTAL PAYMENTS
      Cells(ii +  5 ,  9 ) = dTotalReceipts
      Cells(ii +  5 ,  2 ) = "TOTAL PAYMENTS"
      Range("B" & ii +  5  & ":I" & ii +  5 ).Select
        With Selection.Borders(xlEdgeTop)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
        End With
      Range("B" & ii +  5  & ":I" & ii +  5 ).Select
        With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
        End With
        
      'Rows(CStr(iRowX) + ":" + CStr(iRowX)).Select
      'Selection.RowHeight = 11.25
      'Rows(CStr(iRowY) + ":" + CStr(iRowY)).Select
      'Selection.RowHeight = 11.25
      iRowX = ii
      iRowY = ii +  5 
      
      ii = ii +  5 
    End If
  
    ii = ii +  1 
  Wend
  
 'delete grand total line
 'Mid(Cells(ii, 1), 1, 11) <> "Grand Total"
  Rows(CStr(ii -  2 ) + ":" + CStr(ii)).Select
  Selection.Delete Shift:=xlUp
  


'HEADING ****  Add ColA, O
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Columns("A:A").ColumnWidth =  1 #
    Columns("O:O").ColumnWidth =  1 #
    
'Set Row Heighth
    Rows("1:3").RowHeight =  9 #

'set data font size & row heighth
  Range("A1:M" & ii +  4 ).Select
    Selection.Font.Size =  6 
  Rows("3:" & ii +  4 ).Select
    Selection.RowHeight =  8 . 5 
    
  Rows(CStr(iRowX) + ":" + CStr(iRowX)).Select
    Selection.RowHeight =  11 . 25 
  Rows(CStr(iRowY) + ":" + CStr(iRowY)).Select
    Selection.RowHeight =  11 . 25 
    

 
        'Center and shade heading box
    Range("E1:O3").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    
    

    Range("A1:O3").Select
    With Selection.Interior
        .ColorIndex =  36 
        .Pattern = xlSolid
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
  






End Sub
Sub CheckFlag()
Dim ip As Integer

  bOutFile = False
  Windows("ro_Collection_Status_Summary.csv").Activate
  
  For ip =  1  To  2 
    If UCase(Cells(ip,  1 )) = "PRINT" Then
      bPrint = True
      Rows(CStr(ip) + ":" + CStr(ip)).Select
        Selection.Delete Shift:=xlUp
    End If
  Next ip
   
  If InStr(Cells( 1 ,  1 ), ":\") >  0  Then
    bOutFile = True
  Else
    Rows("1").Insert
  End If
  
  
End Sub
Sub OutFile()

  'Copy Sheet1 to a new workbook in the directory from
  'the cell A1 in the csv file. Copy without the macros.
  'Then close Workbook .
    
    
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy
    Workbooks( 2 ).Activate

    'If Dir(sOutFile) <> "" Then Kill sOutFile
    ActiveWorkbook.SaveAs Filename:=sOutFile, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    
    'Windows(Dir(sOutFile)).Activate
    ActiveWorkbook.Close ( 0 )
        
End Sub

Sub TitleFormat()
'
' TitleFormat recorded 10/12/1999
'

'Insert top 10 rows
    Rows("1:10").Select
    Selection.Insert Shift:=xlDown
'Set row heighths
    Rows("1:1").Select
    Selection.RowHeight =  7 . 5 
    Rows("2:8").Select
    Selection.RowHeight =  8 . 75 
    Rows("9:9").Select
    Selection.RowHeight =  7 . 5 
    Rows("10:10").Select
    Selection.RowHeight =  9 #
    
'Put bold box around all top 9 rows
    Range("A1:O9").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
'put light box around inside edge
    Range("B2:N8").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
'Add right side title text, bold

    Cells( 3 ,  14 ) = "SCHEDULE 9C"
    Cells( 4 ,  14 ) = "    -SUMMARIES"
    Cells( 5 ,  14 ) = sTitle4
    Cells( 6 ,  14 ) = sAddr1
    Cells( 7 ,  14 ) = sCsz

    Range("N3:N7").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Arial"
        .Size =  6 
    End With
    Selection.HorizontalAlignment = xlLeft

    
'Add title box

    ActiveSheet.Shapes.AddShape(msoShapeRectangle,  206 . 25 ,  14 . 25 ,  276 . 75 ,  45 #). _
        Select
    Selection.Characters.Text = _
        sTitle1 & Chr( 10 ) & sTitle2 & Chr( 10 ) & sTitle3
    With Selection.Characters(Start:= 1 , Length:= 115 ).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size =  12 
    End With
    Selection.HorizontalAlignment = xlCenter
    
'Add shading to edge of title
    Range("A1:O1").Select
    Selection.Interior.ColorIndex =  36 
    Range("A9:O9").Select
    Selection.Interior.ColorIndex =  36 

    Range("O1:O9").Select
    Selection.Interior.ColorIndex =  36 
    Range("A1:A9").Select
    Selection.Interior.ColorIndex =  36 

End Sub

Sub SheetFormat()
'
' Macro recorded 10/12/1999 by LK
'

'
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "&""Arial,Bold""&8PAGE   &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints( 0 . 5 )
        .RightMargin = Application.InchesToPoints( 0 . 5 )
        .TopMargin = Application.InchesToPoints( 0 . 3 )
        .BottomMargin = Application.InchesToPoints( 0 . 51 )
        .HeaderMargin = Application.InchesToPoints( 0 . 62 )
        .FooterMargin = Application.InchesToPoints( 0 . 2 )
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
       ' .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom =  100 
    End With
        'set repeating title with 1 extra row for clear space
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$14"
    End With
    
    
        'merge opening/closing balance headers
    Range("L11:M11").Select    'closing
    With Selection
        .HorizontalAlignment = xlCenter
        .MergeCells = False
    End With
    Selection.Merge
    Range("L12:M12").Select     'closing balance
    With Selection
        .HorizontalAlignment = xlCenter
        .MergeCells = False
    End With
    Selection.Merge
    Range("E11:F11").Select     'opening
    With Selection
        .HorizontalAlignment = xlCenter
         .MergeCells = False
    End With
    Selection.Merge
    Range("E12:F12").Select     'opening balance
    With Selection
        .HorizontalAlignment = xlCenter
        .MergeCells = True
    End With
    Selection.Merge


End Sub

Sub Add_Logo()
'
' Macro2 Macro
' Macro recorded 05/25/2000 by ysi
' logo must be in default path L:\yardi\sdefpath

'
    ActiveSheet.Pictures.Delete
   
    Range("B3").Select
    ActiveSheet.Pictures.Insert("L:\yardi\sdefpath\logo_irg.gif").Select
    Selection.ShapeRange.ScaleWidth  0 . 9 , msoFalse, msoScaleFromBottomRight
    Selection.ShapeRange.ScaleWidth  0 . 83 , msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight  0 . 81 , msoFalse, msoScaleFromTopLeft
    Range("B10").Select

End Sub