VBA/Macro Automatically remove lines in tables on different tabs that start with employee ID

kizzie37

Well-known Member
Joined
Oct 23, 2007
Messages
575
Office Version
  1. 365
I think this is a simple enough request but I'm not sure how to execute simply and flawlessly.

I have a workbook with multiple tabs, each tab contains a table

Tab names (exactly as listed):

Misc Info
IT Equipment & Phones
Computer Equipment
Education & Training
Committees
Facilities
Atlas
CurrentWorker

The first cell/column in each tab contains an Employee ID (column labelled ID)

When I Type an ID number into the Tab "Remove Employee" Cell C3 I would like the macro to delete the line in each table on each of the tabs listed above that is associated with that ID number.

Can anyone assist with this please.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
How about this placed in the "Remove Employee" module...

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Target = Range("C3") Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    Dim Worksheet As Object, ws As Worksheet
    Dim tbl As ListObject, rw As Long
    Dim nam As String, r As Range
    nam = Target.Value
    For Each ws In worksheets
        If Not ws.Name = "Remove Employee" Then
            Set tbl = ws.ListObjects(1)
            Set r = ws.ListObjects(1).DataBodyRange.Columns(1).Find(nam, LookAt:=xlWhole)
            If Not r Is Nothing Then
                rw = r.Row - tbl.HeaderRowRange.Row
                 tbl.ListRows(rw).Delete
            End If
        End If
    Next
    
End Sub
 
Upvote 0
How about this placed in the "Remove Employee" module...

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Target = Range("C3") Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    Dim Worksheet As Object, ws As Worksheet
    Dim tbl As ListObject, rw As Long
    Dim nam As String, r As Range
    nam = Target.Value
    For Each ws In worksheets
        If Not ws.Name = "Remove Employee" Then
            Set tbl = ws.ListObjects(1)
            Set r = ws.ListObjects(1).DataBodyRange.Columns(1).Find(nam, LookAt:=xlWhole)
            If Not r Is Nothing Then
                rw = r.Row - tbl.HeaderRowRange.Row
                 tbl.ListRows(rw).Delete
            End If
        End If
    Next
   
End Sub
Ok what am I doing wrong I have inserted a module into the developer tab of the "Remove Employee" Tab, pasted the code and saved the WB as Xlsm, but when I go to try and run the macro, there isnt one in the list?
 
Upvote 0
In the VBE, you do not need a new module. Double click on worksheet "Remove Employee" on the left hand side under Microsoft Excel Objects., that will open the "Remove Employee" module. You want to paste the code there. The code is triggered by a Worksheet Event. In this case, that event will be you changing the value of Cell C3 on the Remove Employee tab.
 
Upvote 0
In the VBE, you do not need a new module. Double click on worksheet "Remove Employee" on the left hand side under Microsoft Excel Objects., that will open the "Remove Employee" module. You want to paste the code there. The code is triggered by a Worksheet Event. In this case, that event will be you changing the value of Cell C3 on the Remove Employee tab.
Thanks, that's a new one on me, I get a run time error on this:
1665521482276.png
 
Upvote 0
Does every worksheet have a table and only one table...
Yes, but I have other worksheets in the workbook that have tables that dont require removal from. I only want removal from those tabs listed.
 
Upvote 0
Ok, give me a couple to re-work the code. As a suggestion, we cannot see your workbook. In your OP you were pretty explicit with the wording of the description of your workbook. I matched the code to what was explicitly stated in your OP.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top