CODEWITHSUNDEEP
vbaexcel
Palash Gangal
Palash Gangal

Reputation: 11

Use a part of macro code independently

I have one doubt! I have a code for consolidation of 22 sheets in a workbook. Now I want to prepare separate buttons for each sheet so that if the user wants they can consolidate only sheets required by them and not all 22 sheets.

I only know the way by creating 22 separate module having part of the codes related to each sheet. Is there any other concise way to do it which do not make me prepare 22 separate modules?

Code example:

sheets("AT").select "whatever code that was required"

Sheets("DE").select "whatever code that was required"

and so on.... Do let me know if question is not clear.

Upvotes: 0

Views: 36

Answers (1)

AndyW
AndyW

Reputation: 430

You only need a single module. When you click a button on a sheet it will be on the activesheet. Therefore you will only need to act on the activesheet and not every sheet in the workbook.

If I was doing the job I would create a userform with a List of worksheet names that the user can select and then the module will step through each selected worksheet name in the list and do whatever actions you need. A checkbox for whole workbook action would also be useful.

Make a new Userform with a commandbutton and also a list box called "myListBox" and ensure that the MultiSelect property is set to multi and not single then add the following code. This will step through each sheet in the workbook and adds the name to the listbox. Once you select a number of names and click the command button it will print the selected names to the Immediate window

Private Sub UserForm_Initialize() 
Dim wks As Worksheet 
    For Each wks In ActiveWorkbook.Worksheets 
        Me.myListBox.AddItem wks.Name 
    Next 
End Sub 

Private Sub CommandButton1_Click() 
    For i = 0 To myListBox.ListCount - 1 
        If myListBox.Selected(i) Then 
            Debug.Print myListBox.List(i)
        End If 
    Next i 
End Sub

Upvotes: 1

Related Questions