To capture ListBox value for array: Sheets(Array("Sheet1", "Sheet3")).Copy

[Karthik Thandapani]

Experts,

A quick question;
If we can get listbox1 value to select a sheet the below way;
Sheets(ListBox1.Value).Copy

How to get listbox1 value to select multiple sheet where I have array
.Sheets(Array("Sheet1", "Sheet3")).Copy

Please help.

 

[NARAYANK991]

Hi Karthik ,

See if this link helps :

http://dailydoseofexcel.com/archives/2004/04/27/using-multiselect-listboxes/

Narayan

 

[Karthik Thandapani]

@NARAYANK991 ; it didn't help. That was actually about multi-select listbox which was done already from my side.
The present requirement is to capture the selected value say sheet name into the VBA code;
.Sheets(Array("Sheet1", "Sheet3")).Copy

If I select sheet1 and sheet3 from the listbox, that should capture like the above shown method. Any help?

 

[NARAYANK991]

Hi Karthik ,

The most disappointing words a member wants to hear are :

it didn't help.

Try this :

Private Sub ListBox1_LostFocus()
            Dim Selected_Sheets As String
            Dim Sheets_Array As Variant
            Dim i As Integer
         
            With Me
                For i = 1 To .ListBox1.ListCount
                    If .ListBox1.Selected(i - 1) Then
                        Selected_Sheets = Selected_Sheets & "," & .ListBox1.List(i - 1)
                    End If
                Next
                Selected_Sheets = Mid(Selected_Sheets, 2)
                Sheets_Array = Split(Selected_Sheets, ",")
               
                Application.DisplayAlerts = False
                ThisWorkbook.Worksheets(Sheets_Array).Copy
                Application.DisplayAlerts = True
            End With
End Sub

Narayan

 

[Karthik Thandapani]

thanks a lot @NARAYANK991 . Awesome!