|
Excel具有強(qiáng)大的數(shù)據(jù)處理和打印輸出功能,，并且易學(xué)易用,，是廣大用戶喜歡使用的電子表格處理軟件。現(xiàn)在一些單位的財(cái)會(huì)人員喜歡用Excel打印本單位的職工工資總表與工資條,，但在Excel中要將工資總表手工地轉(zhuǎn)換為工資條則是一件比較煩瑣的事,，下面是編寫的一個(gè)Excel宏，運(yùn)行這個(gè)宏就可將編輯好了的工資總表很方便地轉(zhuǎn)換為工資條打印輸出,。 在Excel中新建一個(gè)文件,，將其命名為“工資表與工資條”，在工作表“sheet1”中輸入并編輯好本單位職工工資總表后,，點(diǎn)擊“工具”菜單→“宏”→“宏…”→輸入宏名“生成工資條”→創(chuàng)建,，輸入如下的宏的各行文本，輸入完成后保存該宏,。將工作表“sheet1”復(fù)制為另一個(gè)工作表“sheet2”中,，使“sheet2”成為當(dāng)前工作表，執(zhí)行剛才創(chuàng)建的宏,，即可很快將表1所示的工資表轉(zhuǎn)換為表2所示的工資條,。 宏的內(nèi)容如下： Sub 生成工資條() Cells.Select '選擇整個(gè)表去掉表格線 Range("F1").Activate Selection.Borders(xlDiagonalDown).Line Style = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Rows("2:2").Select '選擇第2行 Selection.Insert Shift:=xlDown '在第2行前插入一行，保持第2行 為選中狀態(tài) num=1 50 '總?cè)藬?shù)×3,，如工資表中有100人則 為100×3即num=300 col=14 '工資表的欄數(shù),，如工資表有17欄則 'col=17 num1 = 4 Do While num1 '循環(huán)插入空行 Range(Cells(num1, 1), Cells(num1, col)).Select '選中第num1行的第1列到第col列 Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown num1 = num1 + 3 Loop Range(Cells(1, 1), Cells(1, col)).Select Application.CutCopyMode = False '剪切復(fù)制模式無效 Selection.Copy '復(fù)制選擇區(qū)域 Range("A2").Select '選擇A2單元格 ActiveSheet.Paste '從A2單元格起粘貼內(nèi)容 num2 = 5 Do While num2 '循環(huán)插入標(biāo)題行 Range(Cells( 1, 1), Cells(1, col)).Select Application.CutCopyMode = False Selection.Copy Cells(num2, 1).Select ActiveSheet.Paste num2 = num2 + 3 Loop Range(Cells(2, 1), Cells(3, col)).Select Application.CutCopyMode = False Selection.Borders(xlDiagonalDown).LineStyle = xlNone '定義表格邊框線、內(nèi)線樣式 Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlDash .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlDash .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Copy Range(Cells(5, 1), Cells(6, col)).Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False '接上行刪除上行尾的連字符 _,，復(fù)制表格線樣式 num3 = 8 Do While num3 ＜= num '循環(huán)復(fù)制表格線樣式 Range(Cells(num3, 1), Cells(num3 + 1, col)) .Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False num3 = num3 + 3 Loop Rows("1:1").Select '刪除多余的一行 Selection.Delete End Sub 以后每月要打印工資表與工資條時(shí),，只需將“工資表與工資條”文件打開，修改好工作表“sheet1”中的當(dāng)月的工資總表數(shù)據(jù)后將其復(fù)制為工作表“sheet2”,，并使“sheet2”成為當(dāng)前工作表,，執(zhí)行宏“生成工資條”即可。 |
|
|
