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.
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