|
发表于 2021-3-7 17:36
|
显示全部楼层
本楼为最佳答案
Sub 提取工资()
Dim arr, x%, n%, brr
n = 1
arr = Sheets("1月汇总").Range("a7:z" & Sheets("1月汇总").[a65536].End(3).Row)
'身份证号4,姓名5,总收入18,住房公积金23,职业年金26,各数据在数组中的位置
ReDim brr(1 To UBound(arr), 1 To 5)
For x = 1 To UBound(arr)
If arr(x, 2) = "工资" Then
brr(n, 1) = arr(x, 5)
brr(n, 2) = "'" & arr(x, 4)
brr(n, 3) = arr(x, 18)
brr(n, 4) = arr(x, 23)
brr(n, 5) = arr(x, 26)
n = n + 1
End If
Next
Dim wb
Set wb = Workbooks.Add
With wb.Sheets(1)
.[a1:e1] = Array("姓名", "身份证号码", "总收入", "住房公积金", "职业年金")
.[a2].Resize(UBound(brr), UBound(brr, 2)) = brr
.Columns("A:E").EntireColumn.AutoFit
End With
wb.SaveAs Filename:="D:\2323.xlsx"
End Sub
身份证号前加个单引号可以转成文本格式,另存为的路径可以在代码中修改。 |
|