Excel VBA Create Workbook and populate with sheets

Question

(Excel 2007)

I have two pieces of code.

One creates and saves a new Excel workbook:

Sub NewWorkBook()

Set NewBook = Workbooks.Add
   With NewBook
   .SaveAs Filename:="NewBook.xlsx"
   End With

End Sub

The other creates worksheets:

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

I need to use these to create 'Workbook B' and populate it with worksheets, but all the code must run from 'Workbook A'.

I can get the first sub to create the workbook, but the CreateWorksheets sub will only work on the workbook containing the code - and then only once I have deleted all but one sheet.

Any ideas?

I'm pretty sure that if you qualify your objects' up to what workbook they are in, in your second macro, that will work - e.g., instead of using If n > Worksheets.Count Then use If n > Workbooks("NewBook.xlsx").Worksheets.Count Then — eirikdaude

Harassed Dad Harassed Dad · Accepted Answer · 2017-09-18T11:52:02

 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

Excel VBA Create Workbook and populate with sheets

1 Answers