I am new to VBA and am finding my way through googling and sticking together answers I find to previous questions posted in forums.
I have two tables on two sheets. These are for a staff timetable. The first sheet “Schedule” with table “TblStaffSched” is a grid of the whole year with everyone’s schedule on. This is where the schedule is adjusted whenever things change (data validation from sheet “dashboard” and complete conditional formatting still to come). The second sheet is “StaffInfo”with table “TblStaff” where staff details such as their names, duties and staff ID # are inputted. On the StaffInfo sheet is a button to “Delete Staff Member.” When this is clicked, an input box comes up to input the staff name. If the Staff Name is found, I would like to delete the whole row for that staff member on both tables and then add a row to the bottom of both tables so that the total number of rows is always 50- it’s what the boss wants! On the table “TblStaffSched” there are formulas to reference “TblStaff” so that the staff name appears on both tables in the same order.
I have found a way to delete rows in multiple sheets from this thread: mrexcel.com/board/threads/vba-to-delete-rows-in-multiple-sheets.938971/
Which works to delete rows, but then when I try to insert rows at the bottom of the tables using:
ActiveSheet.ListObjects("tblstaff").ListRows.Add AlwaysInsert:=True
It only adds them to the first table “TblStaff,” and sometimes two rows.
I know how to loop through tables:
Dim tbl As ListObject
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
For Each tbl In sht.ListObjects
'Do something to all the tables...
Next tbl
Next sht
So I am thinking the problem is the way I am deleting rows is not actually for tables, but for ranges and this is the problem. Should I be using different code to delete rows from tables?
Once this is sorted, I plan to protect the sheets so that staff members can only be added by clicking on the add staff member button and a userform opening or deleted using the buttons on the StaffInfo Sheet.
There are other sheets that will be in the final workbook, but to keep the size down for the forum, I have removed them.
Thank you for your help everyone!
Bookmarks