Option Explicit
Option Base 1
Public Sub Tabel()
'aequit 13.02.2020
Dim lastCol&, lLastRow&, i&, k&, l&, n&
Dim a(), fio$, tabn&
Dim oWS As Worksheet, oWS_Itog As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set oWS_Itog = Worksheets("Итог")
oWS_Itog.Cells.Clear
For l = 1 To ThisWorkbook.Worksheets.Count - 1
Set oWS = Worksheets(l)
lastCol = oWS.Cells(2, oWS.Columns.Count).End(xlToLeft).Column
lLastRow = oWS.Cells(oWS.Rows.Count, 2).End(xlUp).Row + 3
ReDim a(lLastRow - 2, lastCol - 1)
For i = 3 To lLastRow
If oWS.Cells(i, 2) <> Empty Then
fio$ = oWS.Cells(i, 2)
tabn& = oWS.Cells(i, 3)
End If
a(i - 2, 1) = fio$
a(i - 2, 2) = tabn&
For k = 3 To lastCol - 1
a(i - 2, k) = oWS.Cells(i, k + 1)
Next k
Next i
lLastRow = oWS_Itog.Cells(Rows.Count, 2).End(xlUp).Row
If lLastRow = 1 Then lLastRow = 0
For i = 1 To UBound(a)
oWS_Itog.Cells(i + lLastRow, 2) = a(i, 1)
oWS_Itog.Cells(i + lLastRow, 3) = a(i, 2)
For k = 4 To UBound(a, 2)
oWS_Itog.Cells(i + lLastRow, k) = a(i, k - 1)
Next k
Next i
Next l
k = oWS_Itog.[B1].CurrentRegion.Columns.Count
l = oWS_Itog.[B1].CurrentRegion.Rows.Count
oWS_Itog.[B1].CurrentRegion.Sort oWS_Itog.[B1], xlAscending, Header:=xlNo
For i = 1 To l Step 4
n = n + 1
oWS_Itog.Cells(i, 1) = n
oWS_Itog.Range(oWS_Itog.Cells(i, 1), oWS_Itog.Cells(i + 3, 1)).MergeCells = True
oWS_Itog.Range(oWS_Itog.Cells(i, 2), oWS_Itog.Cells(i + 3, 2)).MergeCells = True
oWS_Itog.Range(oWS_Itog.Cells(i, 3), oWS_Itog.Cells(i + 3, 3)).MergeCells = True
Next i
oWS_Itog.Rows(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
oWS_Itog.[a1] = "№" & Chr(10) & "п/п"
oWS_Itog.[B1] = "Ф. И. О."
oWS_Itog.[C1] = "таб. №"
With oWS_Itog.[B1].CurrentRegion
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.LineStyle = True
End With
oWS_Itog.Range(oWS_Itog.Cells(2, 2), oWS_Itog.Cells(l + 1, 2)).HorizontalAlignment = xlLeft
For i = 4 To k + 1
oWS_Itog.Cells(1, i) = "час."
Next i
oWS_Itog.Columns("A:A").EntireColumn.AutoFit
oWS_Itog.Columns("B:B").EntireColumn.AutoFit
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub |