+ Reply to Thread
Results 1 to 5 of 5

Delete and add rows to multiple tables on multiple sheets using inputbox

  1. #1
    Registered User
    Join Date
    11-22-2019
    Location
    UK
    MS-Off Ver
    PROFESSIONAL 2013
    Posts
    8

    Delete and add rows to multiple tables on multiple sheets using inputbox

    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!
    Attached Files Attached Files
    Last edited by Oliver_watkins; 02-02-2020 at 09:51 PM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    8,033

    Re: Delete and add rows to multiple tables on multiple sheets using inputbox

    Couple of clarification questions....
    1) The other sheets that you've deleted, will these also contain tables that names need deleteing from or is it just the two tables mentioned in your post?
    2) What happens if you have two people with the same name?

    BSB

  3. #3
    Registered User
    Join Date
    11-22-2019
    Location
    UK
    MS-Off Ver
    PROFESSIONAL 2013
    Posts
    8

    Re: Delete and add rows to multiple tables on multiple sheets using inputbox

    Hi BSB,

    Thanks for the quick response.

    1) Good point! There will actually be one other table with a row that needs deleting- a "count" table on another sheet which shows how many of each duty/ leave type staff have over the year. I was intending to use the "TargetSheetName" Function in the VBA originally to specify the three sheets to go through, but that was before I realised I should be using tables. But that is it.

    2) I was planning on using data validation so that is not possible, but I could also use the employee ID number as they are already unique and assigned by the company HR department.

    Thanks

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    8,033

    Re: Delete and add rows to multiple tables on multiple sheets using inputbox

    I need to pop out for a short while but will have a think about this in the mean time and will post back soon if nobody else has in the mean time.

    I would very much recommend using the employee ID if possible. It will make your life far easier in the long run.

    BSB

  5. #5
    Registered User
    Join Date
    11-22-2019
    Location
    UK
    MS-Off Ver
    PROFESSIONAL 2013
    Posts
    8

    Re: Delete and add rows to multiple tables on multiple sheets using inputbox

    Thanks BSB

    Yes, I am happy to use the employee id instead, will just mean a little bit more training for users in how to delete staff but I agree that in the long run, it'll be better.

    I did try the macro recorder, but it's the looping through tables/sheets that I got stuck at.

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to insert multiple rows with inputbox on multiple sheets?
    By smasma in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-03-2018, 01:43 AM
  2. Replies: 2
    Last Post: 06-22-2018, 04:23 PM
  3. [SOLVED] delete multiple rows from multiple sheets
    By Trebor777 in forum Excel General
    Replies: 4
    Last Post: 10-16-2017, 10:26 AM
  4. [SOLVED] Delete the same rows on multiple sheets
    By turtlesrun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2015, 07:31 AM
  5. Delete rows. Inputbox multiple criteria
    By vbanooby in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2011, 09:25 AM
  6. Delete the same rows on multiple sheets
    By mon whimpy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-16-2008, 07:21 AM
  7. Delete Rows from multiple Sheets.
    By drbobsled in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-06-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1