Excel VBA应用:把员工资料表转化为厂牌打印格式 图文

2018年6月14日15:55:01Excel VBA应用:把员工资料表转化为厂牌打印格式 图文已关闭评论 1,696 views

员工资料表

Excel VBA应用:把员工资料表转化为厂牌打印格式,只需1秒!

厂牌打印格式

Excel VBA应用:把员工资料表转化为厂牌打印格式,只需1秒!

实现方法

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

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

鸣谢:若有疑问,请留言或评论,小编帮您解说!!

  • 微信扫码免费学习
  • 免费学习48小时
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin