output_format

output_format.bas

Public BlockMatchRow(100, 100) As Integer
Public WhoMax(100, 100) As Integer
Public cell_index(2) As Integer


Sub Run()
    sheets_number = 3
    tv_number = 5 'counter
    start_sheet = 2
    SetArray sheets_number, start_sheet, tv_number
    'get_range_index_start_cells 2, 1
    'get_range_index_start_cells 2, 2
    'get_range_index_start_cells 2, 3
    'get_range_index_start_cells 2, 4
    get_range_index_start_cells 2, 5
    
    get_range_index_start_cells 3, 5
    get_range_index_start_cells 4, 5

    
    'MsgBox WhoMax(1, 1)
    'MsgBox WhoMax(1, 4)
    'Copy_paste_selection copy_sheet_index, copy_start_row, copy_start_col, copy_end_row, copy_end_col, paste_sheet_index, paste_start_row, paste_start_col
    'tv = 2
    'st = 1
    'sheet_start
    'Copy_paste_selection st + sheet_start, WhoMax(st, tv + 1) - WhoMax(st, tv), 1, 1 + WhoMax(1, 1), 6 * -6, paste_sheet_index, paste_start_row, paste_start_col
End Sub


Function SetArray(sheets_number, sheet_start_index, tv_number)
    counter = Application.WorksheetFunction.CountIf(Sheets(1).[E:E], "=sch-Date")
    For c = sheet_start_index To sheet_start_index + sheets_number
        Sheets(sheet_start_index).Select
        Cells.Select
        lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
        lLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column


        Dim varArray() As Integer

        ReDim Preserve varArray(sheet_start_index + sheets_number)
    
        j = 2
        BlockMatchRow(c, 1) = 0
        For i = 1 To lLastRow
            If Cells(i, 2) = "Proposal" Then BlockMatchRow(c, j) = i - 2: j = j + 1: 'MsgBox BlockMatchRow(c, j)
        Next i
    
        
        For a = 2 To counter
            WhoMax(c, a) = BlockMatchRow(c, a) - BlockMatchRow(c, a - 1)
            'MsgBox WhoMax(c, a)
        Next a

        varArray(c) = WhoMax(c, tv_number)
    Next c
    SetArray = Application.WorksheetFunction.Max(varArray())
End Function



Function Copy_paste_selection(copy_sheet_index, copy_start_row, copy_start_col, copy_end_row, copy_end_col, paste_sheet_index, paste_start_row, paste_start_col)
    Sheets(copy_sheet_index).Select

    Worksheets(sheet_index).Range(Cells(copy_start_row, copy_start_col), Cells(copy_end_row, copy_end_col)).Select
    Selection.Copy
    
    Sheets(paste_sheet_index).Select
    Worksheets(Sheets.Count).Cells(paste_start_col, paste_start_row).Select
    ActiveSheet.Paste
End Function

Function get_range_index_start_cells(sheet_index, tv_number)
    s_col = 0
    s_row = 0
    'If range_number = 1 Then s_col = 0: s_row = 0
    If tv_number > 1 Then s_row = BlockMatchRow(sheet_index, tv_number)
    cell_index(1) = s_row
    cell_index(2) = s_col
    'MsgBox cell_index(1)
    
    get_range_index_start_cells = cell_index()
End Function


Function get_range_index_end_cells(sheet_index, range_number)

End Function