Copying a certain amount of Worksheets based on input from a Userform

[erica_090]

Hello, I'm very new to VBA and I'm trying to copy a certain amount of Worksheets into a Workbook based on input that a user puts in a textbox on a userform. Basically, I have multiple users that will be using the same template. Depending on the user will depend on how many template worksheets they will need in their Workbook. Some users could require only 2 or 3 and some could need up to 80 template worksheets so I have a textbox in a Userform set up to ask how many template worksheets will be required. Can someone tell me the code in order to do this? Is this something that is possible the way that I'm trying to do it? Should I be using something other than a textbox in a Userform to accomplish this?

 

[JLGWhiz]

You can use a textbox. The input will need to be converted to an integer value after the user makes their entry. You will need a macro to get the value to work in the execution of making the sheets..
This should get you started.

Private Sub TextBox1_Change()
Dim vl As Variant
If Me.TextBox1.Text > "" Then
    vl = Me.TextBox1.Text
    vl = CLng(vl)
    For i = 1 To vl
        Sheets("myTplt").Copy After:=Workbooks("Other").Sheets(Workbooks("Other").Sheets.Count) 'Edit workbook name
        Workbooks("Other").Sheets(Workbooks("Other").Sheets.Count).Name = "NewSheet" & i 'give it a name
    Next
End Sub

Be sure the testbos name is correct and the code goes into the UserForm code module. Just right click on the userform in design mode and then click 'ViewCode to access th3e code module.

 

[My Aswer Is This]

So if I understand you would like something like this.
1. A user open a Userform.
2. User puts 5 in Userform TextBox1
3. User clicks on Command Button
4. And now Active Workbook sheet named "Template" is copied 5 times to another Open Workbook

Is this what your wanting?

And how would the script know what the WorkBook we are copying sheets to.
Some how we would have to tell the script what the workbook name is.
And you do realize both Workbooks would need to be open.