(Excel 2007)
我有两个代码。
一个人创建并保存了一个新的Excel工作簿:
Sub NewWorkBook()
Set NewBook = Workbooks.Add
With NewBook
.SaveAs Filename:="NewBook.xlsx"
End With
End Sub
其他创建工作表:
Public Sub CreateWorksheets()
Dim n As Integer
n = 1
Dim WrkSheet As Worksheet
For n = 1 To 31
If n > Worksheets.Count Then
Set WrkSheet = Sheets.Add(After:=Sheets(Worksheets.Count))
Else
Set WrkSheet = Sheets(Worksheets.Count)
End If
WrkSheet.Name = n
Next n
End Sub
我需要使用这些来创建"工作簿B"并用工作表填充它,但是所有代码都必须从" Workbook A"中运行。
我可以获取第一个子来创建工作簿,但是createWorksheets子仅在包含代码的工作簿上工作 - 然后只有删除除了一张纸以外的所有内容后才可以使用。
有什么想法?
Sub NewWorkBookAndSheets()
Dim NewBook As Workbook
Dim n As Integer
Dim start As Integer
Set NewBook = Workbooks.Add
With NewBook
start = .Worksheets.Count + 1
if start < 31 then
For n = start To 31
.Sheets.Add After:=Sheets(n - 1)
Next n
For n = 1 To 31
.Worksheets(n).Name = n
Next n
end if
.SaveAs Filename:="NewBook.xlsx"
End With
End Sub