VBA实战——加班记录数据整理

Sub 加班记录数据整理()

Application.Calculation = xlCalculationManual '关闭自动计算以加快运行速度

Dim i, imax, j, k1, k2 As Integer

Sheets("Sheet2").Select '建立一维表标题字段

Range("A1").Value = "员工"

Range("B1").Value = "年"

Range("C1").Value = "月"

Range("D1").Value = "日"

Range("E1").Value = "小时数"

k = 2 * Day(DateSerial(Year(Now), Month(Now), 1 - 1))

imax = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

j = 2

For i = 3 To imax Step 1

Sheets("Sheet1").Select

Range(Cells(i, 2), Cells(i, 63)).Select '逐个复制单个员工加班小时数到一维表

Application.CutCopyMode = False

Selection.Copy

Sheets("Sheet2").Activate

Cells(j, 5).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=True

Range(Cells(j, 1), Cells(j + 61, 1)).Value = Sheets("sheet1").Cells(i, 1).Value '复制员工名字到一维表

Range(Cells(j, 2), Cells(j + 61, 2)).Value = Year(Now() - 20) '一维表填入年、月、日

Range(Cells(j, 3), Cells(j + 61, 3)).Value = Month(Now() - 20)

For k1 = 1 To 62 Step 1

Cells(j + k1 - 1, 4).Value = Int((k1 - 1) / 2) + 1

Next k1

j = j + 62

Next i

Sheets("Sheet2").Activate '筛选非零数据行

Range("E1").Select

Application.CutCopyMode = False

Selection.AutoFilter

ActiveSheet.Range("$A$1:$E$2000").AutoFilter Field:=5, Criteria1:="<>"

Application.Calculation = xlCalculationAutomatic '重新打开自动重算功能

End Sub