I need to apply a VBA to multiple sheets, but cant figure it out.

Question

Hello,

I'm looking to find a VBA that moves rows to another sheet based on a checkbox being checked. I need to do the same thing with multiple sheets, and there can be 2 destinations. In my example, I've got a submittal log that has different sheets for divisions. I then have one sheet for submittals that need to be resubmitted and one for submittals that have been approved. On the division sheets I have 2 columns with checkboxes, one column is a checkbox for when it's approved then there is another column for when it needs to be resubmitted.

Is there a VBA that can move the submittals to either the approved or resubmit sheets based on what check mark is checked?

This last part isn't necessary, but if there is a way to move them back to the sheet they came from and be in the same spot they were in before that would be a great plus.

Answer 1

Yep, you should be able to do this with VBA. Use a Worksheet_Change event on each division sheet that watches the cells linked to the checkboxes. The checkboxes should be Form Control checkboxes (not ActiveX) and each one must be linked to a cell. The linked cell will return TRUE when checked and FALSE when unchecked.

Let's assume column H is the linked cell for “Approved” and column I is the linked cell for “Resubmit”. The data you want to move is in columns A through G. You can adjust ranges as needed.

Place this code inside each division sheet module (right-click sheet tab, View Code):

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wsApproved As Worksheet
    Dim wsResubmit As Worksheet
    Dim lastRow As Long
    Dim destRow As Long
    
    Set wsApproved = ThisWorkbook.Sheets("Approved")
    Set wsResubmit = ThisWorkbook.Sheets("Resubmit")
    
    If Intersect(Target, Range("H:I")) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    lastRow = Target.Row
    
    If Cells(lastRow, "H").Value = True Then
        destRow = wsApproved.Cells(wsApproved.Rows.Count, "A").End(xlUp).Row + 1
        Rows(lastRow).Columns("A:G").Copy wsApproved.Rows(destRow).Columns("A")
        Rows(lastRow).Delete
    ElseIf Cells(lastRow, "I").Value = True Then
        destRow = wsResubmit.Cells(wsResubmit.Rows.Count, "A").End(xlUp).Row + 1
        Rows(lastRow).Columns("A:G").Copy wsResubmit.Rows(destRow).Columns("A")
        Rows(lastRow).Delete
    End If
    
    Application.EnableEvents = True

End Sub

This code checks whether a change happened in columns H or I. If Approved is checked, the row is copied to the next empty row on the Approved sheet and then deleted from the division sheet. If Resubmit is checked, it moves to the Resubmit sheet instead.

If you want this to work across multiple division sheets without duplicating code, you can move the logic into a standard module and call it from each sheet’s Worksheet_Change event.

For moving rows back to their original sheet and original position, you would need to store that information before moving the row. The simplest method is to add two hidden helper columns that store the original sheet name and original row number before moving. For example, before copying:

Cells(lastRow, "J").Value = Me.Name
Cells(lastRow, "K").Value = lastRow```

Then when moving back from Approved or Resubmit, read those stored values:

Dim originalSheet As Worksheet
Dim originalRow As Long

originalRow = Cells(lastRow, "K").Value
Set originalSheet = ThisWorkbook.Sheets(Cells(lastRow, "J").Value)

Rows(lastRow).Columns("A:K").Copy originalSheet.Rows(originalRow).Columns("A")
Rows(lastRow).Delete

That should place the row back where it originally came from. You should ensure that the original row is still empty or insert the row before pasting.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin