自定义创建工作簿,可以指定创建的工作簿有几个工作表(有更简单的方法,比如设置Application.SheetsInNewWorkbook = sheet_num后再设置回来)
第二个自定义函数是方便以某一工作表创建后直接赋值工作簿
'创建工作簿,指定有多少个工作表
Function CreateWorkbook(Optional ByVal sheet_num As Integer = 1) As Workbook
Dim n As Integer, count As Integer, diff As Integer
Dim wb As Workbook
Set wb = Workbooks.Add
count = wb.Sheets.count
If count < sheet_num Then
Do Until count = sheet_num
wb.Sheets.Add After:=wb.Sheets(count)
count = count + 1
Loop
Else
Application.DisplayAlerts = False
Do Until count = sheet_num
wb.Sheets(count).Delete
count = count - 1
Loop
Application.DisplayAlerts = True
End If
'
Set CreateWorkbook = wb
End Function
'复制某一工作表创建工作簿
Function CreateWorkbookFromSheet(sh As Worksheet)
Dim wb As Workbook
Set wb = CreateWorkbook(1)
sh.Copy After:=wb.Sheets(1)
Application.DisplayAlerts = False
wb.Sheets(1).Delete
Application.DisplayAlerts = True
Set CreateWorkbookFromSheet = wb
End Function