Adapt macro to run on several different, selected worksheets

Occasional Contributor

Hi,

 

I do not have any experience with VBA, but the attached, simple macro, that I created using the recording function, would help me a lot in my work.

The macro is doing what I want it to do, but I would like to adapt it so that I can run it on several different, selected worksheets in one go.

Is there anybody that can help me with this?

 

Regards,

Kristel

1 Reply

@KristelNulens 

First, a general note on VBA programming, because it appears that you are not using Option Explicit.  To instruct the VBA compiler to require you to declare all variables, start each code module (i.e., worksheet module, standard module, or class module) with an Option Explicit statement. If you do not have an Option Explicit statement in a module, a misspelled variable name will cause a new variable (of type Variant) to be created, possibly with unexpected and hard-to-understand results.  OTOH, with the Option Explicit statement in place, the VBA compiler will stop at the top of the procedure and complain (helpfully).

 

You usually declare a variable in a Dim statement.  (Sometimes Private or Static statements are appropriate alternatives.)

 

If you do any significant amount of VBA coding, check this checkbox under the VBA window menu Tools | Options... to have the editor add Option Explicit to all newly-created modules.

MyVBEOptions.png

 

However, adding an Option Explicit statement to an existing module can cause the code there to "fail" (again, to have the compiler stop and complain). You will have to test the effect on each module for which you add the statement. If a problem arises (at runtime), you need to either create the required variable declaration(s), or temporarily comment out the Option Explicit statement.

 

Now, regarding your macro... I believe you should create a second procedure (with a meaningful name, like ReworkSelectedSheets, preferably qualifying Sheets ... I don't know ... MedSheets or something more specific), and use it as a macro; it will invoke your current macro once for each user-selected worksheet.

Public Sub ReworkSelectedSheets()

    Dim vntItem     As Variant
    Dim colSelectedSheets   As Collection
    Dim strSheetName    As String
    Dim objWorksheet    As Worksheet
    
    '----   Build a collection of names of the selected sheets.
    Set colSelectedSheets = New Collection
    For Each vntItem In ActiveWindow.SelectedSheets
        colSelectedSheets.Add vntItem.Name
    Next vntItem
    
    '----   Do the rework (column deletion, column header renaming...) for each
    '       of those sheets.
    ActiveSheet.Select  'to clear the multiselection, which might _
            be a problem or cause a problem with future code
    For Each vntItem In colSelectedSheets
        Set objWorksheet = Sheets(vntItem)
        objWorksheet.Activate
        Call DataListing_RAVE_1501_1
        Call MsgBox("Rework completed for worksheet " & vntItem _
                , vbInformation Or vbOKOnly, "ReworkSelectedSheets")
    Next vntItem
    
    '----   Clean up.
    Set objWorksheet = Nothing
    Set colSelectedSheets = Nothing

End Sub

 

The code in the DataListing_RAVE_1501_1 procedure appears to be functional without any required change. However, I would still recommend the following changes:
Near the top of the procedure, define the variables (I see only two) used within:

Dim MR      As Range
Dim Cell    As Range


To speed up processing, add this statement near the top of the procedure:

    Application.ScreenUpdating = False

...and add this statement just before the End Sub:

    Application.ScreenUpdating = True