copy specific worksheets based on textbox input

hello,

I was able to get some help here before that's why I came back to ask for assistance from you excel experts. Here is the thing. I have a Userform which has a textbox and a command button and my workbook contains 100 worksheets named 1-100. A user enters specific worksheets to copy from the textbox say 5-10. Upon pressing the command button, the worksheets entered on the textbox will be copied and be save as another workbook. How do I go about that? Thanks in advance.

Sincerely,

Becky

Hi, This worked for me, but May need some adjusting for your own situation.
Make sure your Userforms & list Boxes are the right Index number in the code.
The new file with copied sheets is saved to the Desktop, alter as needed.
Place the first code in the "Userform Initilize".
And the second code in the "Userform Command Button"

Make sure the ListBox Properties is set to "Multi Select" (Option2)

This code adds sheet names to the Userform list Box

Private Sub UserForm_Initialize()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
        UserForm3.ListBox1.AddItem sht.Name
    Next sht
End Sub

This code copies selected items (Sheet names) in listBox to An Array then copies the Sheets names (In the Array) from the ActiveWorkbook to a new WorkBook.

Dim i As Integer, Ray(), C As Integer, Pst As Integer
 C = 0
 For i = 0 To UserForm3.ListBox1.ListCount - 1
    If UserForm3.ListBox1.Selected(i) Then
        ReDim Preserve Ray(C)
            Ray(C) = ListBox1.List(i)
                C = C + 1
            End If
    Next i

Dim NewWorkbook As Workbook
Dim Basebook As Workbook, sht As Range
Set Basebook = ThisWorkbook
Set NewWorkbook = Workbooks.Add
Application.SheetsInNewWorkbook = UBound(Ray()) + 4
For Pst = 0 To UBound(Ray())
Basebook.Sheets(Ray(Pst)).Cells.Copy NewWorkbook.Sheets("sheet" & Pst + 1).Range("A1")
Next Pst

NewWorkbook.SaveAs Filename:="C:\Documents and Settings\test\Desktop\MyNewBook.xls"
    Workbooks("MyNewBook.xls").Close

Regards Mick

hi,

First of all I appreciate the response given by Mick. I did tried the code and it appears that the program works by adding another worksheet inside an active workbook and then save it to a new workbook together with the added worksheet. What I would like to do is to simply enter the sheet names in a range inside a textbox say, sheet1 to sheet10 then when I click the command button Go, sheet1 to sheet10 will be saved as a new workbook. Is this possible without having to display the sheets on a listbox just like the code provided by mick?

Sincerely,

Becky

Hi, Becky
Try this:-
Run code, "InputBox" appears, Enter Sheets for copying (1/1, 5/10 etc) click OK, Sheets copied to new Workbook and saved in folder as per original Workbook.

Dim Rng As String, Dn As Range, R, Fst As Integer, Lst As Integer
Dim Pst As Integer, c As Integer, Rep As String
On Error Resume Next
Rng = Application.InputBox(prompt:="Please enter Sheets as 2/2, 1/5, 8/20 etc.", Title:="Copy Sheets to New Workbook", Type:=2)
    Rep = Replace(Rng, "/", "_")
        R = InStr(Rng, "/")

If R = 0 Then
    MsgBox "Please enter Sheets as 2/2, 1/5, 8/20 etc."
    Exit Sub
End If

Fst = Left(Rng, R - 1)
Lst = Right(Rng, Len(Rng) - R)

If Lst > Worksheets.Count Then
    MsgBox "Selection out of Range"
    Exit Sub
End If

Dim NewWorkbook As Workbook
Dim Basebook As Workbook, sht As Range
Dim AddSht As Integer
Set Basebook = ThisWorkbook
Set NewWorkbook = Workbooks.Add

AddSht = Lst - Fst + 1

If AddSht > 3 Then
    AddSht = AddSht + 3
 Else
    AddSht = 0
End If
Application.SheetsInNewWorkbook = AddSht
For Pst = Fst To Lst
c = c + 1
Basebook.Sheets(Pst).Cells.Copy NewWorkbook.Sheets("sheet" & c).Range("A1")
Next Pst

NewWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\NewBk " & Rep & ".xls"

Workbooks("NewBk " & Rep & ".xls").Close

Regards Mick

Hi Mick,

Thanks for helping. Where do I place this code?

Dim Rng As String, Dn As Range, R, Fst As Integer, Lst As Integer
Dim Pst As Integer, c As Integer, Rep As String
On Error Resume Next
Rng = Application.InputBox(prompt:="Please enter Sheets as 2/2, 1/5, 8/20 etc.", Title:="Copy Sheets to New Workbook", Type:=2)
    Rep = Replace(Rng, "/", "_")
        R = InStr(Rng, "/")

If R = 0 Then
    MsgBox "Please enter Sheets as 2/2, 1/5, 8/20 etc."
    Exit Sub
End If

Fst = Left(Rng, R - 1)
Lst = Right(Rng, Len(Rng) - R)

If Lst > Worksheets.Count Then
    MsgBox "Selection out of Range"
    Exit Sub
End If

and this code to?

Dim NewWorkbook As Workbook
Dim Basebook As Workbook, sht As Range
Dim AddSht As Integer
Set Basebook = ThisWorkbook
Set NewWorkbook = Workbooks.Add

AddSht = Lst - Fst + 1

If AddSht > 3 Then
    AddSht = AddSht + 3
 Else
    AddSht = 0
End If
Application.SheetsInNewWorkbook = AddSht
For Pst = Fst To Lst
c = c + 1
Basebook.Sheets(Pst).Cells.Copy NewWorkbook.Sheets("sheet" & c).Range("A1")
Next Pst

NewWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\NewBk " & Rep & ".xls"

Workbooks("NewBk " & Rep & ".xls").Close

Sincerely,

Becky

Hi Becky.
That bit of code is all one piece of code, although its got extra variables half way down.
You need to place it as one code in a "Command Button" in any sheet in the book you want to Copy from. If you don't know how to do that Read on.

Inserting a Command Button in worksheet and Allocating Code

Open your workbook at the Sheet Number with your Data that you want to Manipulate.
Click View, Toolbars, Control ToolBox,---- Control ToolBox Menu Appears on sheet.
Slide you cursor over the Tool Box (Tool Tip Text) until you find a Command Button.
Click It then click Somewhere on the sheet.-- Command Button appears on sheet.
The Command Button will have handles round its edge, This is so you can Position it on the sheet.
Click the command Button "Hold the Mouse Click Down" and Drag the Command Button to where you want it.
Double Click the Command Button, The VB Editor window will appear.

You can also open the Editor By clicking Alt + F11, but if you double click the Command Button the editor will open in the procedure relating specifically to your Command Button.

If the VB Editor window has two panes the right pane is where you must paste your code.

The left pane can be "Project Window" or "Properties Window, Click (Ctrl+R) if not showing.

Paste your code just under the words "Private Sub CommandButton1_Click() " in the Right hand pane.
If you have done this correctly. Scroll to the bottom of the code and you should see the words "End Sub"

On the VB Window, Toolbar you will see a Green Triangular shapes icon,.
This is to change the VB Editor mode from "Run Mode" to "Design Mode "
Click this Icon, The Small blue square to its left will change from light blue to dark blue, or Vice Versa. Before you close the Editor make sure this Square is "Dark Blue" i.e. (Reset)

Sometimes the it will appear Reset when it is not, that why I usually put a message at the bottom of the code, to know if is run or not.(You do not have a msgbox in your code)
Close the Editor. Select the Command Button and Click it.
When the code Runs the Msgbox should appear With The Message "Transfer Complete" ,if this doesn't happen Open The VB Editor "Alt + F11" and click the "Reset ( Blue Square) and or The "Green Triangle" on the Tool bar . The Blue Square should be "Dark Blue ", in order to run the code..
NB:- If you want to get back into this specific code through the Command Button.
Get the Controls ToolBox menu back on the screen, Click the green triangle, When you slide the cursor over your Command button , The cursor shape will change to a "Arrow Headed Cross" , you will then be able to double click it to view your code.
If you prefer, you can forgo all this hassle by putting the code in an MT macro, with a key combination like (Ctrl+"A") to run it.
If you still get a problem , call back
Regards Mick