With me

[Karthik Thandapani]

Hi,

When I run the below code, it's throwing up an error.

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

Previously, I was using this code in an userform and it was working well. Presently, I've moved the code to Module1 and tying to call from the userform, am I doing something incorrectly?
Please recommend any workaround.

p.s: actual logic is to have an userform to show sheet names in listbox. As user selects sheets, this will get copied by array formula.

Thanks,
Karthik

 

[Luke M]

When you moved the code, the Me object no longer refers to anything. The Me object exists in UserForm and Worksheet modules. I'm guessing it needs to be:

With UserForm1

or whatever the name of your actual form is.

 

[Karthik Thandapani]

@Luke M : I modified the code as suggested; but problem at line#2

Compile error:
Method or data member not found.

I unload the userform1 once userform is click and then the below code runs. Any problem with that? Please advise.

With UserForm1
                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

 

[Luke M]

Yes, unloading is the problem. When you Unload a userform, it vanishes into the great cosmos (ie, becomes nothing), and none of it's parts exist. Instead, you should have the button hide the user form (Me.Hide) and then the macro can still use the data stored within the user form. Of course, you would then need to specify within the macro to
Unload UserForm1

So, the two parts would look like

'IN USERFORM MODULE
Private Sub CommandButton1_Click()
'Button to launch/close userform

Me.Hide
Call MyMacro
End Sub

and

'IN A REGULAR MODULE
Sub MyMacro()
With UserForm1
    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
Unload UserForm1
End Sub

 

[Karthik Thandapani]

You're awesome! Thanks @Luke M

 

[Karthik Thandapani]

@Luke M : Type mismatch error when I save & convert this as an add-in and try to run with another workbook.

ThisWorkbook.Worksheets(Sheets_Array).Copy

This wasn't a problem when I try running the code with the actual file in which I'm building add-in. But once I save that as an add-in, attach with another workbook and try to run, issue. Please help

 

[Luke M]

Similar to "Me", ThisWorkbook specifies a particular object (workbook). Specifically, the workbook which the code is contained in. If you have this as an add-in, you probably want to reference ActiveWorkbook, not ThisWorkbook.

 

[Karthik Thandapani]

got it. Thanks @Luke M