Copy the Code

Private Sub CommandButton1_Click()
Application.SheetsInNewWorkbook = 3
'Define a vairable to store number of sheets required in new workbook
Dim j As Integer
'Assigning value to variable., value = number updated in B3 cell
j = ThisWorkbook.Sheets("Sheet2").Range("B3").Value
'equlaizing variable to sheets in workbook property
Application.SheetsInNewWorkbook = j
'Define variable to create new workbook
Dim wkb As Workbook
'Add new workbook
Set wkb = Workbooks.Add
'Define loop variable
Dim i As Integer
'assigin value to for loop
For i = 1 To j
'activate loop sheet
wkb.Sheets(i).Activate
'use WITH to avert complexity in coding
With ThisWorkbook.Sheets("Sheet2")
'loop sheet is less than or equl to number entered in E2 cell value
If i <= .Range("F2").Value Then
'Loop sheet name = text entered in cell E2 and loop iteration number
wkb.Sheets(i).Name = .Range("E2").Value & i
ElseIf i > .Range("F2").Value And i <= .Range("F3").Value Then
wkb.Sheets(i).Name = .Range("E3").Value & i
ElseIf i > .Range("F3").Value And i <= .Range("F4").Value Then
wkb.Sheets(i).Name = .Range("E4").Value & i
ElseIf i > .Range("F4").Value And i <= .Range("F5").Value Then
wkb.Sheets(i).Name = .Range("E5").Value & i
Else
wkb.Sheets(i).Name = .Range("E6").Value & i
End If
End With
Next
'This is very important step., if we ignored this., going forward all the new workbooks open with number stored in "J" variable.
Application.SheetsInNewWorkbook = 3
End Sub

Private Sub CommandButton1_Click()
Application.SheetsInNewWorkbook = 3
Dim j As Integer
j = 50
Application.SheetsInNewWorkbook = j
Dim wkb As Workbook
Set wkb = Workbooks.Add
Dim i As Integer
For i = 1 To j
wkb.Sheets(i).Activate
If i <= 10 Then
wkb.Sheets(i).Name = "purchase " & i
ElseIf i > 10 And i <= 25 Then
wkb.Sheets(i).Name = "Sales " & i
ElseIf i > 25 And i <= 35 Then
wkb.Sheets(i).Name = "Profit " & i
Else
wkb.Sheets(i).Name = "Day" & i
End If
Next
Application.SheetsInNewWorkbook = 3
End Sub