Asking How Many Workbooks to Create

[999HelpPlease]

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?

 

[Norie]

Try this.

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

 

[Joe4]

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.

NumWB = InputBox("How many workbooks do you want to create?")
If NumWB>0 Then
   For i = 1 to NumWB

 

[MattH1]

Try this and let me know if it works.

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

 

[999HelpPlease]

Try this and let me know if it works.

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?

 

[MattH1]

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:

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.

 

[999HelpPlease]

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:

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.

 

[MattH1]

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:

Workbooks(MainWorkBook).Activate

 

[Norie]

I found this code somewhere, can't remember where though, seemed to work for me.

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

 

[999HelpPlease]

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:

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?