如何用VBA批量产生新的EXCEL并命名

如何用VBA批量产生新的EXCEL并命名

批量产生新的EXCEL并按自己的要求命名，可以节省不少时间。

在开发工具——查看代码

将以下代码复制到里面

Sub 批量生成EXCEL并命名()

Dim i As Integer

T = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To T

If Cells(i, 12).Value = Cells(i + 2, 12).Value And Cells(i, 12).Value = Cells(i + 1, 12) Then i = i + 2 Else i = i

If Cells(i, 12).Value = Cells(i + 1, 12).Value Then i = i + 1 Else i = i

Range(Cells(i, 1), Cells(i, 10)).Select

Selection.Copy

Workbooks.Add

Range("A2").Select

ActiveSheet.Paste

Range("A1").Select

ActiveCell.FormulaR1C1 = "序号"

Range("B1").Select

ActiveCell.FormulaR1C1 = "所属ENODE-ID"

With ActiveCell.Characters(Start:=1, Length:=16).Font

.Name = "Tahoma"

.FontStyle = "常规"

.Size = 10

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontNone

End With

Range("C1").Select

ActiveCell.FormulaR1C1 = "基站名称"

Range("D1").Select

ActiveCell.FormulaR1C1 = "站型"

Range("E1").Select

ActiveCell.FormulaR1C1 = "厂商"

Range("F1").Select

ActiveCell.FormulaR1C1 = "维护部"

Range("G1").Select

ActiveCell.FormulaR1C1 = "行政区"

Range("H1").Select

ActiveCell.FormulaR1C1 = "扩容方式"

Range("I1").Select

ActiveCell.FormulaR1C1 = "扩容配置"

Range("J1").Select

ActiveCell.FormulaR1C1 = "来源"

Windows("扩容批量生成脚本.xlsm").Activate

t = Cells(i, 12).Value

ActiveWindow.ActivateNext

ActiveWorkbook.SaveAs Filename:=t '另存

ActiveWorkbook.Close

Windows("扩容批量生成脚本.xlsm").Activate

Next

MsgBox "已完成", vbOKOnly + vbInformation, "弹窗提醒"

End Sub

将其保存为XLSM格式。后面如果要执行就直接点执行即可，不过格式一定要如下图所示。