[已解决]利用单元格里的内容，批量新建工作簿

a78480446

根据sheet２，Ａ列中的内容，新建工作簿，新建的工作簿中要有，“统计表”工作表。请各位老师指教


新建工作簿.rar (7.22 KB, 下载次数: 55)

2014-4-23 11:02 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

dsmch

2014-4-23 13:11

1. Sub Macro1()
   
2. Dim arr, i&, sht As Worksheet, zf$
   
3. Set sht = ThisWorkbook.Sheets("统计表")
   
4. arr = Range("a1").CurrentRegion
   
5. Application.ScreenUpdating = False
   
6. Application.DisplayAlerts = False
   
7. Application.SheetsInNewWorkbook = 1
   
8. For i = 1 To UBound(arr)
   
9.     zf = arr(i, 1) & ".xls"
   
10.     With Workbooks.Add
    
11.         sht.Copy after:=.Sheets(1)
    
12.         .Sheets(1).Delete
    
13.         .SaveAs Filename:=ThisWorkbook.Path & "" & zf
    
14.     End With
    
15.     Workbooks(zf).Close 1
    
16. Next
    
17. Application.SheetsInNewWorkbook = 3
    
18. Application.DisplayAlerts = True
    
19. Application.ScreenUpdating = True
    
20. End Sub
    

复制代码

跳转到最佳答案楼层

冥王

放到某个文件夹下再运行宏  有点慢
建议还是用技巧新建

as0810114

刚学，凑一个玩一下
Sub xj()
Application.DisplayAlerts = False
Dim i%, a%
a = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To a
    Set wb = Workbooks.Add
    wb.SaveAs ThisWorkbook.Path & "\" & Sheet2.Cells(i, 1), 51
    wb.Sheets(1).Name = "统计表"
    wb.Close
Next
Application.DisplayAlerts = True
End Sub

a78480446

谢谢，我下载去看看。

dsmch

1. Sub Macro1()
   
2. Dim arr, i&, sht As Worksheet, zf$
   
3. Set sht = ThisWorkbook.Sheets("统计表")
   
4. arr = Range("a1").CurrentRegion
   
5. Application.ScreenUpdating = False
   
6. Application.DisplayAlerts = False
   
7. Application.SheetsInNewWorkbook = 1
   
8. For i = 1 To UBound(arr)
   
9.     zf = arr(i, 1) & ".xls"
   
10.     With Workbooks.Add
    
11.         sht.Copy after:=.Sheets(1)
    
12.         .Sheets(1).Delete
    
13.         .SaveAs Filename:=ThisWorkbook.Path & "" & zf
    
14.     End With
    
15.     Workbooks(zf).Close 1
    
16. Next
    
17. Application.SheetsInNewWorkbook = 3
    
18. Application.DisplayAlerts = True
    
19. Application.ScreenUpdating = True
    
20. End Sub
    

复制代码

dsmch

……………………

冥王

冥王 发表于 2014-4-23 11:39
放到某个文件夹下再运行宏  有点慢
建议还是用技巧新建

理解错误，以为楼主只是要批量新建工作簿，将里面的一个工作表命名为统计表呢