【无标题】

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