员工资料表

厂牌打印格式

实现方法

按【Alt+F11】组合键，打开VBE窗口，插入一模块，输入如下代码：

Sub 员工资料转化为厂牌打印格式()

Application.ScreenUpdating = False

With Worksheets("厂牌打印格式")

.Select

.Cells.Clear

.DrawingObjects.Delete

Dim shp As Shape

For Each shp In Worksheets("员工资料表").Shapes

shp.Name = shp.TopLeftCell.Offset(0, -3).Value

Next shp

Dim Rng As Range, i As Integer, TargetCell As Range

With Worksheets("员工资料表")

For Each Rng In .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))

i = i + 1

Set TargetCell = Cells(Int((i - 1) / 3) * 6 + 1, ((i - 1) Mod 3) * 3 + 1)

.Range("A1:E1," & Rng.Resize(1, 5).Address).Copy

TargetCell.PasteSpecial Paste:=xlPasteValues, Transpose:=True

.Shapes(Rng.Value).Copy

Sheets("厂牌打印格式").Paste

Selection.Left = TargetCell.Offset(3, 1).Left

Selection.Top = TargetCell.Offset(3, 1).Top

TargetCell.Offset(3, 1).RowHeight = Selection.Height

TargetCell.Offset(3, 1).ColumnWidth = 9.5

TargetCell.Offset(3, 0).VerticalAlignment = xlCenter

TargetCell.CurrentRegion.Borders.LineStyle = xlContinuous

Next Rng

End With

End With

Range("C:C,F:F").ColumnWidth = 3.5

Range("A1").Activate

Application.ScreenUpdating = True

End Sub

然后，单击【运行】命令，1秒钟就可把海量的员工资料表转化为厂牌打印格式！

温馨提醒：在同一工作簿中，新建两张工作表，名称分别为“员工资料表”和“厂牌打印格式”。

鸣谢：若有疑问，请留言或评论，小编帮您解说！！