Deleting a specific row on multiple worksheets using a userform

Question

I am developing a tool to track pupil assessments and am using a userform to manage pupil data.

I have written code based on responses to previous questions to delete the same row on 14 worksheets within the workbook which contain various pupil data but am getting an 'Object required' error at the WshtNameCrnt.Rows(l).Delete line in the code below:

Private Sub cmbDelete_Click()
    Dim WshtNames As Variant
    Dim WshtNameCrnt As Variant

    WshtNames = Array("Pupil Data", "RWM", "Art", "Computing", "Design Technology", "Geography", "History_", _
                        "MFL", "Music", "PE", "RE", "Science", "Bookbands", "KS1 - TRP")

    Dim msgResponse As String    'confirm delete
        Application.ScreenUpdating = False
            msgResponse = MsgBox("This will delete the selected record. Continue?", _
            vbCritical + vbYesNo, "Delete Entry")

    Select Case msgResponse    'action dependent on response

        Case vbYes
            Dim l As Long
            l = ActiveCell.Row 'store currently active cell row

                For Each WshtNameCrnt In WshtNames
                    WshtNameCrnt.Rows(l).Delete
                Next

        'restore form settings
        With Me
            .cmbAmend.Enabled = False    'prevent accidental use
            .cmbDelete.Enabled = False    'prevent accidental use
            .cmbAdd.Enabled = True    'restore use
            'clear form
            Call ClearControls
            Call ResortData
        End With

    Case vbNo
        Exit Sub    'cancelled
    End Select
    Application.ScreenUpdating = True
End Sub

I assume that the issue is that my array of worksheet names is dimensioned as a variant but am not sure of the best way to set this as an object and to loop through the worksheets in the array.

Please can someone advise me if my assumption is correct and suggest/show me how to resolve the issue?

Answer 1

WshtNameCrnt is the name of the Worksheet, not the actual Worksheet object.

So instead of

WshtNameCrnt.Rows(l).Delete

use the Worksheets collection, which works with either the Worksheet index or name, to return the Worksheet object in question.

ThisWorkbook.Worksheets(WshtNameCrnt).Rows(1).Delete