• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Select Worksheet/Worksheets to be copied

T

Thomas Kuriakose

Active Member
Respected Sirs,

We have multiple sheets with data. The summary sheet should have a button, which on activation should give a list of sheets to be copied to a new workbook with a new filename. The option should be to select one sheet for copy or multiple sheets for copy.

I found this code and this is copying and creating a new workbook. The only problem is I need to enter the sheet names each time in the code and copy the code to a new button for selecting different sheets.

Code: 
ption Explicit
Sub TwoSheetsAndYourOut1()
    Dim NewName As String
    Dim nm As Name
    Dim ws As Worksheet
   
    If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
    "New sheets will be pasted as values, named ranges removed" _
    , vbYesNo, "NewCopy") = vbNo Then Exit Sub
   
    With Application
        .ScreenUpdating = False
       

        On Error GoTo ErrCatcher
        Sheets(Array("A Jul", "A Aug")).Copy
        On Error GoTo 0
       
        For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            ws.Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            Cells(1, 1).Select
            ws.Activate
        Next ws
        Cells(1, 1).Select
       
   
        For Each nm In ActiveWorkbook.Names
            nm.Delete
        Next nm
       
        NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
       
     
        ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xlsx"
        ActiveWorkbook.Close SaveChanges:=False
       
        .ScreenUpdating = True
    End With
    Exit Sub
   
ErrCatcher:
    MsgBox "Specified sheets do not exist within this workbook"
End Sub

Kindly help with code to get a selection option for single and multiple sheets in one button click.

Thank you very much,

with regards,
thomas
 

Attachments

Hi Thomas !

If you directly select worksheets on active workbook,
a single codeline is necessary to copy these selected worksheets
to a new workbook !
ActiveWindow.SelectedSheets.Copy
​
 
Respected Sir,

Thank you very much for this information.

Correct me if I am wrong, so we assign this code to one button and when we are inside the workbook, we select the required sheets and this will trigger a new workbook with a file name where the selected sheets will be pasted.

Thank you very much,

with regards,
thomas
 
Respected Sirs,

Apologies for asking again. Today we users started working with the new code, they are finding it difficult to select form 24 different worksheets.

They have requested to provide a list of the sheets in the workbook and on selecting the sheet from this list, it should be saved to new workbook.

Kindly guide on how to get the list selection working.

Thank you very much,

with regards,
thomas
 
First, add an UserForm to your project within a ListBox …

A way to initialize this UserForm :​
Code: 
Private Sub UserForm_Initialize()
         Dim Ws As Worksheet
    For Each Ws In Worksheets
        ListBox1.AddItem Ws.Name
    Next
End Sub
 

In the Properties window of the ListBox, you can change
its ListStyle to 1 - fmListStyleOption and
you must set its MultiSelect to 1 - fmMultiSelectMulti …
​
 
Respected Sir,

ListStyle set to 1 - fmListStyleOption and
MultiSelect set to 1 - fmMultiSelectMulti

upload_2016-7-18_20-28-38.png

Kindly let me know the next steps.

Thank you very much,

with regards,
Thomas
 
• Enlarge ListBox1 Width property to avoid horizontal scroll bar …

• To copy ListBox1 selected worksheets to a new workbook :​
Code: 
Private Sub CommandButton1_Click()
With ListBox1
   ReDim AR%(1 To .ListCount)
    For L& = 1 To .ListCount
        If .Selected(L - 1) Then N& = N& + 1: AR(N) = L
    Next
    If N Then
        If N < .ListCount Then ReDim Preserve AR(1 To N)
        Worksheets(AR).Copy
    End If
End With
    Unload Me
End Sub
Same post #2 Copy method …
​
 
Respected Sir,

Thank you very much for the step by step guide on this procedure.

One run-time error, I don't know whether, I followed your instructions correctly -
upload_2016-7-19_8-24-52.png

upload_2016-7-19_8-25-0.png


Very much appreciated.

Thanks once again,

with regards,
Thomas
 
ListBox1 is known only within the UserForm !

• So within your UserForm, add an OK command button.

• Once created, add the code in the same Userform module
where is located its Initialize event (post #7 code) …
​
 
Respected Sir,

The code is working now when run from the VBA editor, kindly let me know how to link this to a button in a summary sheet which will have only this button to call the user form.

Also, I added a cancel button, but could not write a code to cancel the creation of new workbook or exiting the user form.

Thank you very much once again for all the procedures explained.

Please find attached the workbook.

with regards,
thomas
 

Attachments

You must log in or register to reply here.
Back
Top