Asking How Many Workbooks to Create

999HelpPlease

New Member
Joined
Jul 16, 2014
Messages
35
I need to create a VBA that asks how many workbooks we want to create and then save them numbering them 1.xlsx to however many we end up with.

I have started the macro but I only know how to write it using this and it only gives me 10. I need it to ask how many we are going to create.

Sub 2()
Dim i As Double
For i = 1 To 10
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & i & ".xlsx"
ActiveWorkbook.Close
Next
End Sub

I also have it this way


Dim ProceedStatus As String
ProceedStatus = InputBox("Do you want to create a new workbook ? Yes / No")
Do While LCase(ProceedStatus) = "yes"
Workbooks.Add
ProceedStatus = InputBox("Do you want to create a new workbook ? Yes / No")
Loop
End Sub

They both do a part but neither really work.

Can someone help me out?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Try this.
Code:
Option Explicit

Sub CreateWBs()
Dim I As Long
Dim NoWBs As Long

    NoWBs = Application.InputBox("Please enter the no of workbooks you want to create:", Title:="Create workbooks", Default:=10, Type:=1)
    For I = 1 To NoWBs
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & I & ".xlsx"
        ActiveWorkbook.Close
    Next I
    
End Sub
 
Upvote 0
Just set up an InputBox to ask how many workbooks you would like to create.
Input boxes aren't relegated to Yes/No responses. You can ask for anything.
Code:
NumWB = InputBox("How many workbooks do you want to create?")
If NumWB>0 Then
   For i = 1 to NumWB
 
Upvote 0
Try this and let me know if it works.

Code:
Sub WSCreator()
Dim wsNumber as Integer
wsNumber = InputBox("How many worksheets would you like?")

For i = 1 To wsNumber
 Workbooks.Add
 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & i & ".xlsx"
 ActiveWorkbook.Close
 Next i

EndSub
 
Last edited:
Upvote 0
Try this and let me know if it works.

Code:
Sub WSCreator()
Dim wsNumber as Integer
wsNumber = InputBox("How many worksheets would you like?")

For i = 1 To wsNumber
 Workbooks.Add
 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & i & ".xlsx"
 ActiveWorkbook.Close
 Next i

EndSub

It didn't work. I asked me how many worksheets I want but it didn't open or save them and it wanted to close my active file which I would like left open.

Any suggestions?
 
Upvote 0
It didn't work. I asked me how many worksheets I want but it didn't open or save them and it wanted to close my active file which I would like left open.

Any suggestions?

I don't entirely know if this will work as I'm not able to test it on my end currently, but let me know what happens when you try this:

Code:
Sub WSCreator()

Dim MainWorkBook As String
MainWorkBook = ActiveWorkBook.Name

Dim wsNumber as Integer
wsNumber = InputBox("How many worksheets would you like?")

Dim VarList as New List(of Integer)

For I=1 to wsNumber
   varlist.add(I)
Next I

For i = 1 To wsNumber
Dim Varlist(I) as Workbook
VarList(I).SaveAs Filename:=ThisWorkbook.Path & i & ".xlsx"
 Next I

'For closing all workbooks at the end not your active file
Dim wb As Workbook, MyWB As Workbook
Workbooks(MainWorkBook).
Set myWB = ActiveWorkbook
For each wb in Workbooks
   If wb.Name <> MyWB.Name Then wb.Close
Next

End Sub

This middle bit is choppy and not sure if it'll work but happy to work through it with you. Let me know what happens. The end coding (and the variable up top) should be good, I use it in a code of mine all the time.
 
Upvote 0
I don't entirely know if this will work as I'm not able to test it on my end currently, but let me know what happens when you try this:

Code:
Sub WSCreator()

Dim MainWorkBook As String
MainWorkBook = ActiveWorkBook.Name

Dim wsNumber as Integer
wsNumber = InputBox("How many worksheets would you like?")

Dim VarList as New List(of Integer)

For I=1 to wsNumber
   varlist.add(I)
Next I

For i = 1 To wsNumber
Dim Varlist(I) as Workbook
VarList(I).SaveAs Filename:=ThisWorkbook.Path & i & ".xlsx"
 Next I

'For closing all workbooks at the end not your active file
Dim wb As Workbook, MyWB As Workbook
Workbooks(MainWorkBook).
Set myWB = ActiveWorkbook
For each wb in Workbooks
   If wb.Name <> MyWB.Name Then wb.Close
Next

End Sub

This middle bit is choppy and not sure if it'll work but happy to work through it with you. Let me know what happens. The end coding (and the variable up top) should be good, I use it in a code of mine all the time.


The Dim VarList Line doesn't work and either does the Workbooks(MainWorkBook). line.
 
Upvote 0
I'm not entirely sure how to fix the Dim VarList line to be honest maybe someone can help you.
As for the Workbooks(MainWorkbook) line, it should be:

Code:
Workbooks(MainWorkBook).Activate
 
Upvote 0
I found this code somewhere, can't remember where though, seemed to work for me.
Code:
Option Explicit

Sub CreateWBs()
Dim I As Long
Dim NoWBs As Long

    NoWBs = Application.InputBox("Please enter the no of workbooks you want to create:", Title:="Create workbooks", Default:=10, Type:=1)
    For I = 1 To NoWBs
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & I & ".xlsx"
        ActiveWorkbook.Close
    Next I
    
End Sub
 
Upvote 0
I'm not entirely sure how to fix the Dim VarList line to be honest maybe someone can help you.
As for the Workbooks(MainWorkbook) line, it should be:

Code:
Workbooks(MainWorkBook).Activate

The Dim VarList comes up as Syntax error

This is how is I have it typed

Dim VarList as New List(of Integer)

Is there something wrong with it?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top