员工资料表

厂牌打印格式

实现方法

按【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秒钟就可把海量的员工资料表转化为厂牌打印格式！

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

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