+ Reply to Thread
Results 1 to 3 of 3

Code adding rows to multiple tables in multiple worksheets

  1. #1
    Registered User
    Join Date
    01-27-2017
    Location
    Munich, Germany
    MS-Off Ver
    2010
    Posts
    2

    Post Code adding rows to multiple tables in multiple worksheets

    Hello everyone,

    First of all, I want to make it clear that I'm not experienced in coding or anything. Whenever I need a code, I usually grab parts from different options in forums to then adjust the whole thing to my needs.

    Said that, here is my situation:
    I have a Workbook with multiple worksheets. 1 worksheet, called "Activities list" has a table of activities that should be filled by the user. And 12 other worksheets (Months - January to December) containing each 3 different tables that are "reproducing" the data from that first table in the "Activities list" Worksheet.

    I've recorded and then manually adjusted a code that adds one row to the bottom of all this tables, but probably because of the way it is written, it's taking around 40 seconds to completely run, which for a user adding multiple rows would be annoying. Could anyone help me in making the code shorter and therefore lighter and quicker to run?

    My current code is as it follows:

    ---

    Sub AddRows()
    '
    If MsgBox("Add new Row?(This can only be undone manually)", vbYesNo) = vbNo Then Exit Sub
    Application.ScreenUpdating = False
    Sheets("Activities List").Select
    Sheets("Activities List").ListObjects("Table37").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("January").Select
    Sheets("January").ListObjects("Table1").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("January").ListObjects("Table2").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("January").ListObjects("Table3").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("February").Select
    Sheets("February").ListObjects("Table4").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("February").ListObjects("Table5").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("February").ListObjects("Table6").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("March").Select
    Sheets("March").ListObjects("Table7").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("March").ListObjects("Table8").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("March").ListObjects("Table9").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("April").Select
    Sheets("April").ListObjects("Table10").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("April").ListObjects("Table11").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("April").ListObjects("Table12").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("May").Select
    Sheets("May").ListObjects("Table13").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("May").ListObjects("Table14").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("May").ListObjects("Table15").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("June").Select
    Sheets("June").ListObjects("Table16").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("June").ListObjects("Table17").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("June").ListObjects("Table18").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("July").Select
    Sheets("July").ListObjects("Table19").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("July").ListObjects("Table20").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("July").ListObjects("Table21").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("August").Select
    Sheets("August").ListObjects("Table22").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("August").ListObjects("Table23").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("August").ListObjects("Table24").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("September").Select
    Sheets("September").ListObjects("Table25").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("September").ListObjects("Table26").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("September").ListObjects("Table27").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("October").Select
    Sheets("October").ListObjects("Table28").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("October").ListObjects("Table29").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("October").ListObjects("Table30").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("November").Select
    Sheets("November").ListObjects("Table31").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("November").ListObjects("Table32").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("November").ListObjects("Table33").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("December").Select
    Sheets("December").ListObjects("Table34").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("December").ListObjects("Table35").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("December").ListObjects("Table36").Range.Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Sheets("Activities List").Select
    Range("G2").Select
    Application.ScreenUpdating = True
    MsgBox "Done! Rows added to this and to all other linked tables in this Workbook"
    '
    End Sub

    ----

    Thank you very much!

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Code adding rows to multiple tables in multiple worksheets

    Hi there,

    Without access to your workbook I can't test whether or not the following code will speed things up for you, but try it and see:

    Please Login or Register  to view this content.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    01-27-2017
    Location
    Munich, Germany
    MS-Off Ver
    2010
    Posts
    2

    Re: Code adding rows to multiple tables in multiple worksheets

    Hello Greg, sorry for not answering you directly, Idk why, but neither the "reply" or the "reply with quote" buttons worked.

    The code is working almost perfectly, reducing running time by half. There’s only one detail.

    As I mentioned in the Thread, each of the “months” Worksheets has 3 tables.
    The first two tables are independent of each others, working with data from other workbooks. But the third one is multiplying a fix rate by the data from the second table (Rate of cost per hour multiplied by number of worked hours).

    I think that, as your code is adding the rows to all tables “simultaneously”, the formula from the row added in the third table is multiplying the fix rate by the row under the second table, like if it’s not recognizing the new added row in table 2 and skipping it.

    As you can see in the example:

    Former last row of table 3: =IF(G$5="Internal","",G$6*G68)
    New last row of table 3: =IF(G$5="Internal","",G$6*G70)

    It skips from row G68 to row G70, which is not even in table 2.

    Is there a way we could make this row addition in the third table of each Worksheet after the addition in the first two tables? Like naming all third tables differently and running the code 2 times? Or this would make it two big and therefore slow to run?

    Again, thank you so much! I understand that the Issue is not in the code, but in the table content and that I did not express that difference in the first place.

+ 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. Consolidating Multiple Tables from Multiple Worksheets onto one Worksheet????
    By JB20 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2015, 12:55 PM
  2. [SOLVED] Delete rows based on the value of two columns using VBA code - Multiple worksheets
    By OmniBlue in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-18-2015, 04:24 PM
  3. Applying filters to multiple pivot tables across multiple worksheets
    By gbarragan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-11-2010, 12:05 PM
  4. Adding multiple rows in different tables of the same worksheet
    By JMarchante in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2010, 07:42 AM
  5. Confused about referencing multiple object code rows in other worksheets?
    By slunk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2006, 05:40 PM
  6. [SOLVED] adding rows across multiple worksheets within a single workbook
    By frank.freeman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2005, 06:05 PM

Tags for this Thread

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