制作工资条是每位财务从业者必备技能,今天教大家如何用Excel VBA一键制作工资条。

excel工资表一键生成vba(ExcelVBA一键制作工资条)(1)

工资表数据

思路

1、用Union方法,将表头和标题栏联合到一起,赋值为Rng。

2、循环工资表,再用Union方法,将每行数据和Rng联合到一起,复制到工资条工作表。

excel工资表一键生成vba(ExcelVBA一键制作工资条)(2)

自动生成工资条

代码

Private Sub CommandButton1_Click()

On Error Resume Next

Dim i%, j%, rng As Range, ws As Worksheet

Set ws = Sheets("工资表")

Set rng = Union(ws.Rows(1), ws.Rows(2), ws.Rows(3)) '表头及标题栏

Sheets("工资条").Cells.Clear

j = 1

Application.ScreenUpdating = False

With Sheets("工资条")

For i = 4 To ws.Cells(Rows.Count, 1).End(xlUp).Row

Union(rng, ws.Rows(i)).Copy .Cells(j, 1)

j = j 4

Next i

End With

MsgBox "工资条制作完成!"

Application.ScreenUpdating = True

End Sub

,