Sub 清空_Click()
On Error Resume Next
Set sb = ThisWorkbook.Sheets("无税申报个税导入模板生成器")
sb.Range("A2:I2").ClearContents
sb.Range("K2").ClearContents
sb.Range("C3:G21").ClearContents
sb.Range("A2").Select
End Sub
Sub 生成金三导入模板_Click()
On Error Resume Next
Set sb = ThisWorkbook.Sheets("无税申报个税导入模板生成器")
If [A2] = "" Then
[A2].Select
MsgBox "请输入纳税人识别号"
Exit Sub
ElseIf [b2] = "" Then
[b2].Select
MsgBox "请输入纳税人名称"
Exit Sub
ElseIf [C2] = "" Then
[C2].Select
MsgBox "请输入员工姓名"
Exit Sub
ElseIf [D2] = "" Then
[D2].Select
MsgBox "请选择身份证类型"
Exit Sub
ElseIf [E2] = "" Then
[E2].Select
MsgBox "请输入国籍"
Exit Sub
ElseIf [F2] = "" Then
[F2].Select
MsgBox "请输入身份证号码"
Exit Sub
ElseIf [G2] = "" Then
[G2].Select
MsgBox "请输入收入额"
Exit Sub
ElseIf [H2] = "" Or [I2] = "" Or [J2] = "" Then
MsgBox "日期有误,请重新输入:"
Exit Sub
ElseIf [K2] = "" Then
[K2].Select
MsgBox "请输入经办人姓名"
Exit Sub
End If
'创建公司文件夹
VBA.MkDir (ThisWorkbook.Path & "\" & sb.Range("B2"))
'取得月份数
date1 = sb.Range("H2")
date2 = sb.Range("I2")
months = DateDiff("m", date1, date2) + 1
'设定初始所属期
beginDate = sb.Range("H2")
For i = 1 To months
'打开excel模板
'Dim mbook As Workbook
'Set mbook = ActiveWorkbook
Set mb = Workbooks.Open(ThisWorkbook.Path & "\mb.xls")
mb.Sheets(1).Range("D3") = sb.Range("A2")
mb.Sheets(1).Range("H3") = sb.Range("B2")
mb.Sheets(1).Range("Y3") = "一般行业"
mb.Sheets(1).Range("AB3") = sb.Range("J2")
mb.Sheets(1).Range("AI3") = sb.Range("K2")
'填入变动的所属期
enddate = WorksheetFunction.EoMonth(beginDate, 0)
mb.Sheets(1).Range("M3") = beginDate
mb.Sheets(1).Range("P3") = enddate
'填入不变的数据
For j = 1 To Application.WorksheetFunction.CountA(sb.Range("C2:C21"))
mb.Sheets(1).Range("B" & j + 10) = "是"
mb.Sheets(1).Range("C" & j + 10) = sb.Range("C" & j + 1)
mb.Sheets(1).Range("D" & j + 10) = sb.Range("D" & j + 1) '"201|居民身份证"
mb.Sheets(1).Range("E" & j + 10) = sb.Range("E" & j + 1)
mb.Sheets(1).Range("F" & j + 10) = sb.Range("F" & j + 1)
mb.Sheets(1).Range("G" & j + 10) = " 正常工资薪金"
mb.Sheets(1).Range("H" & j + 10) = beginDate
mb.Sheets(1).Range("I" & j + 10) = enddate
mb.Sheets(1).Range("J" & j + 10) = sb.Range("G" & j + 1)
If beginDate < #10/1/2018# Then
mb.Sheets(1).Range("Y" & j + 10) = "3,500.00"
Else
mb.Sheets(1).Range("Y" & j + 10) = "5,000.00"
End If
mb.Sheets(1).Range("AB" & j + 10) = "0.03(0.00)"
mb.Sheets(1).Range("AD" & j + 10) = "0.00"
Next j
'另存为导入金三的EXCEL文件,并关闭模板
ActiveWorkbook.CheckCompatibility = False
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & mb.Sheets(1).Range("H3") & "\" & Format(mb.Sheets(1).Range("M3"), "yyyy-mm-dd") & ".xls"
ActiveWorkbook.Close
'所属期增加1个月
beginDate = DateAdd("m", 1, beginDate)
Next i
End Sub