微信公众号 【EXCEL880】 QQ群【341401932】
课程咨询 加我微信EXCEL880B 试学网址http://v.excel880.com
课程咨询 加我微信EXCEL880B 试学网址http://v.excel880.com
员工资料表
厂牌打印格式
实现方法
按【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秒钟就可把海量的员工资料表转化为厂牌打印格式!
温馨提醒:在同一工作簿中,新建两张工作表,名称分别为“员工资料表”和“厂牌打印格式”。
鸣谢:若有疑问,请留言或评论,小编帮您解说!!