logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

Excel VBA Macro: Adding Rows in TABLE1 & TABLE2 with Button - Add More Rows Function

kikanek 11277 15
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
| New topic
  • #1 17288182
    kikanek
    Level 10  
    Hi,
    I have a problem, I have two tables in the file called "TABLE1" and "TABLE2"

    I want to add a button that will be under each table and its function will be to add more rows (one row)
    I have a macro like below,
    however, it only works for the first table. For the second one, which is 3 lines below, no longer ...
    How can this be solved?
    Code: text Expand Select all Copy to clipboard
    
Sub WstawWierszTABELA1()

Dim ost&, ile&
ost = Range("Bytowa2").Rows.Count + 3
ile = 1
Rows(ost & ":" & ost + ile - 1).Insert CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

    Thanks for the help
  • ADVERTISEMENT
  • #2 17288221
    lanzul
    Level 30  
    1. Attaching a sample data file - they do not have to be real, but they must have the target data layout in the sheet (s)
    2. The above will also explain exactly whether when you write about tables, you mean data lists or "most ordinary" excel table-tables - data lists have additional vba commands to handle them, ordinary tables only "standard"
  • #3 17288293
    kikanek
    Level 10  
    Thank you for your willingness to help,
    the table is created in excel by "insert tables"

    an example file in the attachment

    in the target version there are other tables below for which I also want to make such a button
    Attachments:
  • #4 17288319
    lanzul
    Level 30  
    So these are letters, and yet they add poems "by themselves" ...?
    Are you planning to insert something only manually, or can you "by some mechanism" transfer something to the list / list from another place of the sheet / notebook?
  • ADVERTISEMENT
  • #5 17288337
    kikanek
    Level 10  
    I want the macro to add another row in the table after the button (above the "SUM" row). So that each table has its own button
    I had to save the above file xlsx because xlsm did not pass here.
  • #6 17288392
    lanzul
    Level 30  
    Oki, but if you place the cursor in a given table, you already have a blank input line prepared by default, this is how table-list works.
    Excel VBA Macro: Adding Rows in TABLE1 & TABLE2 with Button - Add More Rows FunctionNowy wiers...tabeli.jpg Download (22.31 kB)

    1. Do you mean, then, that apart from the line with an asterisk, the macro will insert an additional empty line as "next" (then there will be two empty lines, including one with an asterisk)?
    2. Is it enough just to place the cursor on the line with an asterisk (after making an entry, a new one will appear anyway with an asterisk)?
  • #7 17288406
    kikanek
    Level 10  
    1. I click the "Add line" button
    2. The macro adds one row above the total row, leaving cell formatting. In this case, it numbers the next row in the first column from the machine and leaves the second empty

    Run the macro that is in the file, you will see.
    The problem is that when I insert this macro and assign a range to it, "Table2" inserts me into the first table anyway
    Excel VBA Macro: Adding Rows in TABLE1 & TABLE2 with Button - Add More Rows Function
  • ADVERTISEMENT
  • #9 17288582
    kikanek
    Level 10  
    It's great, and tell me how to make the same macro "add add line" so that it adds 5 lines at a time

    I would like to give you a button to delete lines as too much is added
  • Helpful post
    #10 17289178
    lanzul
    Level 30  
    A little macro rework:
    tabela..zip Download (18.43 kB)
    Lines can be added from a strictly defined range that you set by yourself ("If ilewrs> 10 Then Exit Sub").
    Delete rows only one at a time, from the last one to keep order.
  • #11 17289912
    kikanek
    Level 10  
    It's really awesome :)

    Tell me, is there any chance to add a line of code to delete so that the first line in the table cannot be deleted?
  • Helpful post
    #12 17290444
    lanzul
    Level 30  
    Chance (?) ... not rather not ... but it is possible ... :) ... to add a line of code to prevent deletion of the 1st line, one of the following:
    Code: text
    Log in, to see the code

    tabela..zip Download (18.38 kB)
  • ADVERTISEMENT
  • #13 17689965
    jend
    Level 1  
    I have a similar problem as a colleague .. I have a dozen or so tables in one sheet .. I need to add 2 lines to each of them .. is it possible to insert two lines from the machine under each table, in the left column to enter the text FROM and the right column it would be empty. So, just like colleagues, the lines are numbered consecutively, instead of the numbering, the word FROM and the second column are empty and in the second line the word DO and the column are empty. screen in the attachment Excel VBA Macro: Adding Rows in TABLE1 & TABLE2 with Button - Add More Rows Function
  • #14 17690285
    lanzul
    Level 30  
    jend wrote:
    ... add 2 lines to each of them ... from the automat, insert two lines under each table ...

    That is, where to insert ... into the table or under the table?
    Prepare an example with a minimum of 3-4 tables on the worksheet.
  • #15 17883627
    mario123321123321
    Level 1  
    I'd like to use your solution with the "add line" macro, but I can't handle it (I'm totally green in Excel).

    When I want to attach your macro, I get the error "Run-time error '9'. Subscript out of range" and after clicking debug I get this:
    Excel VBA Macro: Adding Rows in TABLE1 & TABLE2 with Button - Add More Rows Function

    can you write step by step how to implement your macro in my sheet?
  • #16 17890684
    lanzul
    Level 30  
    Include any authoritative example that raises this error, maybe it's just a matter of the appropriate table name (variable nameTBL) or maybe something else.
| New topic
Report a violation of the law

Topic summary

The discussion revolves around creating an Excel VBA macro to add rows to two tables, "TABLE1" and "TABLE2," using buttons placed beneath each table. The initial macro provided only functions for "TABLE1," leading to inquiries about modifying it to work for both tables. Users seek clarification on whether the tables are standard Excel tables or data lists, and how to ensure the macro adds rows correctly above the total row while maintaining formatting. Suggestions include checking button code names for proper table identification and modifying the macro to add multiple rows or prevent deletion of the first row. Additional users express similar needs for adding rows to multiple tables and seek guidance on implementing the macro without encountering errors.
Summary generated by the language model.
ADVERTISEMENT