excel宏设置之一键生成多张sheet并写入内容与格式
提示：excel先开启宏设置！
（一）效果：
第一张表，有宏按钮
结果图：
说明：生成的sheet包括窗口冻结，数据有效性，背景以及字体颜色，单元格宽高，字段名设置等
代码如下：（第一次写，望指点）
单位名称不能为数字
Sub 按钮2_Click()' 批量新建多个sheet表，新建一个cresheet的宏Dim a As Integer '定义a变量Dim strArr As Variant '定义字符数组Dim alpArr As Variant '定义字母数组Dim length '用length表示数组的长度a = 5 '初始值，从第5行开始，可以更改Set st = Worksheets("纳税企业申报信息表") ' 表初始值，定位源数据表，可以更改'while开始根据本表D5列生成sheetDo While st.Cells(a, "D") <> "" ' 设定循环条件，从纳税企业申报信息表中的D5开始，如果数据不为空，执行该循环On Error Resume Next ' 若表名不存在，忽略代码引起的运行错误'if每一次循环生成一个新sheet并设置好里面的内容If Worksheets(st.Cells(a, "D").Value) Is Nothing Then '判断是否存在对应的工作表Worksheets.Add after:=Worksheets(Worksheets.Count) '永远将新表加入到最后一个工作表之后ActiveSheet.Name = st.Cells(a, "D").Value '新的工作表为当前活动的工作，将工作表的名称更改为纳税企业申报信息表中对应单元格的名字。'单元格设置内容并设置水平垂直居中Range("A1").Value = "企业名称："Range("A1").HorizontalAlignment = Excel.xlLeftRange("A1").VerticalAlignment = Excel.xlCenterRange("B1").Value = st.Cells(a, "D").ValueRange("B1").HorizontalAlignment = Excel.xlLeftRange("B1").VerticalAlignment = Excel.xlCenterRange("A2").Value = "纳税人识别号："Range("A2").HorizontalAlignment = Excel.xlLeftRange("A2").VerticalAlignment = Excel.xlCenterRange("B2").Value = st.Cells(a, "E").ValueRange("B2").HorizontalAlignment = Excel.xlLeftRange("B2").VerticalAlignment = Excel.xlCenter'单元格设置高Range("A1").RowHeight = 30Range("A2").RowHeight = 30Range("A3").RowHeight = 30Range("A4").RowHeight = 40'列名strArr = Array("序号", "工号", "*姓名", "*证照类型", "*证照号码", "*国籍(地区)", "*性别", "*出生日期", "*人员状态", "*任职受雇从业类型", "手机号码", "任职受雇从业日期", "*本期收入", "本期免税收入", "离职日期", "个人投资额", "个人投资比例(%)", "基本养老保险费", "基本医疗保险费", "失业保险费", "住房公积金", "累计子女教育", "累计继续教育", "累计住房贷款利息", "累计住房租金", "累计赡养老人", "企业(职业)年金", "商业健康保险", "税延养老保险", "其他", "准予扣除的捐赠额", "免税额", "是否残疾", "是否烈属", "是否孤老", "残疾证号", "烈属证号", "是否境外人员", "中文名", "涉税事由", "出生国家(地区)", "首次入境时间", "预计离境时间", "其他证照类型", "其他证照号码", "户籍所在地（省）", "户籍所在地（市）", "户籍所在地（区县）", "户籍所在地（详细地址）", "居住地址（省）", "居住地址（市）", "居住地址（区县）", "居住地址（详细地址）", "联系地址（省）", "联系地址（市）", "联系地址（区县）", "联系地址（详细地址）", "电子邮箱", "学历", "开户银行", "银行账号", "职务", "备注")alpArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK")'列长度length = UBound(strArr) - LBound(strArr) + 1'for循环在sheet生成列名For i = 0 To (length - 1)Range(alpArr(i) + "4").Value = strArr(i)Range(alpArr(i) + "4").Interior.Color = RGB(204, 204, 255) '单元格背景颜色Range(alpArr(i) + "4").ColumnWidth = 10 '单元格设置宽Range(alpArr(i) + "4").HorizontalAlignment = Excel.xlCenter '文字水平垂直居中Range(alpArr(i) + "4").VerticalAlignment = Excel.xlCenterNextRange(Cells(5, 1), Cells(500, 63)).Borders.LineStyle = xlDash '单元格边框格式虚线'下面是冻结窗口Set wd = ActiveWindowWith wd.SplitColumn = 13.SplitRow = 4End WithActiveWindow.FreezePanes = TrueSet wd = Nothing'冻结窗口完毕'设置字体颜色为红色Range(Cells(4, 3), Cells(4, 13)).Font.Color = RGB(255, 0, 0)'设置数据有效性With [e4:e4].Validation '证照号码提示.Delete.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="1".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "当证照类型为""居民身份证"",请输入18位居民身份证号".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [c4:c4].Validation '*姓名提示.Delete.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="1".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "证照类型为外国护照时，录入证件上的英文姓名，其他证件录入中文姓名".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [f4:f4].Validation '*国籍提示.Delete.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="1".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "证照类型为""居民身份证""时，允许为空".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [g4:g4].Validation '*性别提示.Delete.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="1".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "证照类型为""居民身份证""时，允许为空".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [h4:h4].Validation '*出生日期提示.Delete.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="1".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "证照类型为""居民身份证""时，允许为空。请严格按照如下格式填写：2018-12-12".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [k4:k4].Validation '手机号码提示.Delete.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="1".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "当""任职受雇从业类型""为""雇员""时必须填写。请输入11位手机号码。".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [l4:l4].Validation '任职受雇从业日期提示.Delete.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="1".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "当""任职受雇从业类型""为""雇员""、""保险营销员""和""证券经纪人""时必须填写。请严格按照如下格式填写:2018-12-12".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [d5:d65536].Validation '*证照类型下拉.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="居民身份证,中国护照,港澳居民来往内陆通行证,台湾人民来往大陆通行证,外国护照".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [f5:f65536].Validation '*国籍下拉.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="中国,中国澳门,中国香港,中国台湾".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [g5:g65536].Validation '*性别下拉.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="男,女".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [i5:i65536].Validation '*人员状态下拉.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="正常,非正常".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith [j5:j65536].Validation '*任职受雇从业类型下拉.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="雇员,保险营销员,证券经纪人,其他".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With'数据有效性设置完毕'if结束End Ifa = a + 1 '行号加1，继续新增下一个'while结束LoopMsgBox ("生成个税工资表完毕，请前往填写！")End Sub
赞 (0)