用Excel宏实现工资表巧转工资条.doc

1、用Excel宏实现工资表巧转工资条 2002/05/1015:42 赛迪网-中国电脑教育报文/邓亚思Excel具有强大的数据处理和打印输出功能，并且易学易用，是广大用户喜欢使用的电子表格处理软件。现在一些单位的财会人员喜欢用Excel打印本单位的职工工资总表与工资条，但在Excel中要将工资总表（如表1）手工地转换为工资条（如表2）则是一件比较烦琐的事，下面是我编写的一个Excel宏，运行这个宏就可将编辑好了的工资总表很方便地转换为工资条打印输出。表1工资表编号日期姓名职务工资各种补贴应领数扣水电其他扣款实发数001200109邓卫平432.50525.50958.0080.5015862.5

2、0002200119成文勇425.00500.00925.5075.0015835.50表2工资条编号日期姓名职务工资各种补贴应领数扣水电其他扣款实发数001200109邓卫平432.50525.50958.0080.5015862.50编号日期姓名职务工资各种补贴应领数扣水电其他扣款实发数002200109成文勇425.50500.50925.5075.0015835.50在Excel中新建一个文件，将其命名为“工资表与工资条”，在工作表“sheet1”中输入并编辑好本单位职工工资总表（如表1所示）后，点击“工具”菜单“宏”“宏”输入宏名“生成工资条”创建，输入如下的宏的各行文本，输入完成后

3、保存该宏。将工作表“sheet1”复制为另一个工作表“sheet2”中，使“sheet2”成为当前工作表，执行刚才创建的宏，即可很快将表1所示的工资表转换为表2所示的工资条。宏的内容如下：Sub 生成工资条()Cells.Select选择整个表去掉表格线Range(F1).ActivateSelection.Borders(xlDiagonalDown).LineStyle = xlNoneSelection.Borders(xlDiagonalUp).LineStyle = xlNoneSelection.Borders(xlEdgeLeft).LineStyle = xlNoneSelec

4、tion.Borders(xlEdgeTop).LineStyle = xlNoneSelection.Borders(xlEdgeBottom).LineStyle = xlNoneSelection.Borders(xlEdgeRight).LineStyle = xlNoneSelection.Borders(xlInsideVertical).LineStyle = xlNoneSelection.Borders(xlInsideHorizontal).LineStyle = xlNoneRows(2:2).Select 选择第2行Selection.Insert Shift:=xlD

5、own 在第2行前插入一行，保持第2行 为选中状态num=150 总人数3，如工资表中有100人则为1003即num=300col=14 工资表的栏数，如工资表有17栏则 col=17num1 = 4Do While num1 = num 循环插入空行Range(Cells(num1, 1), Cells(num1, col).Select选中第num1行的第1列到第col列Selection.Insert Shift:=xlDownSelection.Insert Shift:=xlDownnum1 = num1 + 3LoopRange(Cells(1, 1), Cells(1, col)

6、.Select Application.CutCopyMode = False 剪切复制模式无效Selection.Copy 复制选择区域Range(A2).Select选择A2单元格ActiveSheet.Paste从A2单元格起粘贴内容num2 = 5Do While num2 = num 循环插入标题行Range(Cells(1, 1), Cells(1, col).Select Application.CutCopyMode = FalseSelection.CopyCells(num2, 1).SelectActiveSheet.Pastenum2 = num2 + 3LoopRan

7、ge(Cells(2, 1), Cells(3, col).Select Application.CutCopyMode = FalseSelection.Borders(xlDiagonalDown).LineStyle = xlNone 定义表格边框线、内线样式Selection.Borders(xlDiagonalUp).LineStyle = xlNoneWith Selection.Borders(xlEdgeLeft).LineStyle = xlDouble.Weight = xlThick.ColorIndex = xlAutomaticEnd WithWith Selecti

8、on.Borders(xlEdgeTop).LineStyle = xlDouble.Weight = xlThick.ColorIndex = xlAutomaticEnd WithWith Selection.Borders(xlEdgeBottom).LineStyle = xlDouble.Weight = xlThick.ColorIndex = xlAutomaticEnd WithWith Selection.Borders(xlEdgeRight).LineStyle = xlDouble.Weight = xlThick.ColorIndex = xlAutomaticEnd

9、 WithWith Selection.Borders(xlInsideVertical).LineStyle = xlDash.Weight = xlThin.ColorIndex = xlAutomaticEnd WithWith Selection.Borders(xlInsideHorizontal).LineStyle = xlDash.Weight = xlThin.ColorIndex = xlAutomaticEnd WithSelection.CopyRange(Cells(5, 1), Cells(6, col).Select Selection.PasteSpecial

10、Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 接上行删除上行尾的连字符 _，复制表格线样式num3 = 8Do While num3 = num循环复制表格线样式Range(Cells(num3, 1), Cells(num3 + 1, col).SelectSelection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=Falsenum3 = num3 + 3LoopRows(1:1).Select删除多余的一行Selection.De