|
发表于 2021-3-28 16:04
|
显示全部楼层
本楼为最佳答案
这个很简单的 n=1 删了 把 n=n+1 放在判断后的第一行- Sub 导出薪金基数18_Click()
-
-
- Application.DisplayAlerts = False
- Application.ScreenUpdating = False
- ActiveSheet.Select '选择当前活动表
- ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
- , AllowFiltering:=True, AllowUsingPivotTables:=True
- ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
- False, AllowFiltering:=True, AllowUsingPivotTables:=True
- ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
- False, AllowFiltering:=True, AllowUsingPivotTables:=True
- ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
- True, AllowFiltering:=True, AllowUsingPivotTables:=True
- ActiveSheet.Unprotect
-
- Dim arr, x%, n%, brr
- Dim aa
- aa = ActiveSheet.Range("b2:w2").Value
-
- arr = ActiveSheet.Range("a4:w" & Range("C65536").End(3).Row)
- '定义新表的列数
- ReDim brr(1 To UBound(arr), 1 To 22)
- For x = 1 To UBound(arr)
- If arr(x, 5) = "" Then Exit For
- If arr(x, 6) <> 0 Then
- n = n + 1
- brr(n, 1) = arr(x, 2)
- brr(n, 2) = arr(x, 3) '即姓名列
- brr(n, 3) = arr(x, 4)
- brr(n, 4) = arr(x, 5)
- brr(n, 5) = arr(x, 6)
- brr(n, 6) = arr(x, 7)
- brr(n, 7) = arr(x, 8)
- brr(n, 8) = arr(x, 9)
- brr(n, 9) = arr(x, 10)
- brr(n, 10) = arr(x, 11)
- brr(n, 11) = arr(x, 12)
- brr(n, 12) = arr(x, 13)
- brr(n, 13) = arr(x, 14)
- brr(n, 14) = arr(x, 15)
- brr(n, 15) = arr(x, 16)
- brr(n, 16) = arr(x, 17)
- brr(n, 17) = arr(x, 18)
- brr(n, 18) = arr(x, 19)
- brr(n, 19) = arr(x, 20)
- brr(n, 20) = arr(x, 21)
- brr(n, 21) = arr(x, 22)
- brr(n, 22) = arr(x, 23)
-
- End If
- Next
-
- Dim yf '创建变量
- Dim nf '创建变量
- yf = [g1] '=f1单元格的内容-月份
- nf = [e1] '年份=e1单元格的内容
-
- '查找并创建“个税测算”文件夹
- If Len(Dir("e:" & nf & "导出", vbDirectory)) < 1 Then
- MkDir "e:" & nf & "导出" '创建新文件夹
- End If
-
- '创建一个新工作簿
- Dim wb
- Set wb = Workbooks.Add
- With wb.Sheets(1)
- .Columns("D").NumberFormatLocal = "@"
-
- '表头各列对应的字段:
- .[a1:v1] = aa
- .[a2].Resize(n, 22) = brr
- .Columns("A:v").EntireColumn.AutoFit
- Range("a1:v" & Range("c65536").End(3).Row).Borders.LineStyle = 1
-
- End With
- wb.SaveAs "e:" & nf & "导出" & yf & "月初薪金-" & Format(Now(), "mmdd-hhmmss") & ".xlsx" '保存在“某年导出”文件夹中,文件名=d1+月+原表名+导出日期
- wb.Close
-
- MsgBox "OK,月初薪金已导出,并保存在“e:" & nf & "导出”文件夹中!", vbyesonly, "温馨提示"
-
- Application.ScreenUpdating = True '刷屏功能
- Application.DisplayAlerts = True '弹窗警示
-
- End Sub
复制代码 |
|