Select Worksheet/Worksheets to be copied

[Thomas Kuriakose]

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.

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

 

[Marc L]

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
​

 

[Thomas Kuriakose]

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

 

[Marc L]

It does a copy of selected worksheets in a brand new workbook :
you must save this new workbook to name it …

​

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided.

with regards,
thomas

 

[Thomas Kuriakose]

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

 

[Marc L]

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

A way to initialize this UserForm :​

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

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this input.

Kindly find attached the user form added to the project.

Thanks once again.

with regards,
thomas

 

[Marc L]

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

 

[Thomas Kuriakose]

Respected Sir,

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



Kindly let me know the next steps.

Thank you very much,

with regards,
Thomas

 

[Marc L]

• Enlarge ListBox1 Width property to avoid horizontal scroll bar …

• To copy ListBox1 selected worksheets to a new workbook :​

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 …
​

 

[Thomas Kuriakose]

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 -





Very much appreciated.

Thanks once again,

with regards,
Thomas

 

[Marc L]

As code works on my side, it seems you do not copy it
in same UserForm module as previous code ?
​

 

[Thomas Kuriakose]

Respected Sir,

Apologies if I am not doing this correctly. Kindly let me know where I am wrong for this error.

Please find attached the file for your reference.

Thank you very much,

with regards,
thomas

 

[Marc L]

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) …
​

 

[Thomas Kuriakose]

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

 

[Marc L]

As any UserForm, call its Show method in the button code :

UserForm1.Show …
​

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for your patience in explaining this in details to me.

Much appreciated.

with regards,
thomas

 

[Marc L]

In case of you do not yet see this : User Forms & Controls in VBA

____________________________________________________
Despite reading, practicing is also learning …
​