【VBA】创建工作簿自定义函数_sheetsinnewworkbook

自定义创建工作簿，可以指定创建的工作簿有几个工作表（有更简单的方法，比如设置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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35