Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Asking How Many Workbooks to Create
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Asking How Many Workbooks to Create

    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?

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,190
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Asking How Many Workbooks to Create

    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
    If posting code please use code tags.

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Asking How Many Workbooks to Create

    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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular
    Join Date
    Jul 2016
    Posts
    174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Asking How Many Workbooks to Create

    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 by MattH1; Dec 15th, 2016 at 12:45 PM.

  5. #5
    New Member
    Join Date
    Jul 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Asking How Many Workbooks to Create

    Quote Originally Posted by MattH1 View Post
    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?

  6. #6
    Board Regular
    Join Date
    Jul 2016
    Posts
    174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Asking How Many Workbooks to Create

    Quote Originally Posted by 999HelpPlease View Post
    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.

  7. #7
    New Member
    Join Date
    Jul 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Asking How Many Workbooks to Create

    Quote Originally Posted by MattH1 View Post
    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.

  8. #8
    Board Regular
    Join Date
    Jul 2016
    Posts
    174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Asking How Many Workbooks to Create

    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

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,190
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Asking How Many Workbooks to Create

    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
    If posting code please use code tags.

  10. #10
    New Member
    Join Date
    Jul 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Asking How Many Workbooks to Create

    Quote Originally Posted by MattH1 View Post
    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?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •