Add rows to tables

    Hi All,
    Not sure if this next task is possible or not?


    I have a command button which copies a hidden master sheet to create a new customer worksheet. On this new sheet there is a table (Scope) of personal information, purchase information and formulas.
    There is another command button which adds a new line to the table (a new purchase record) which works perfectly (code below) but when I add a new sheet for another customer, the code doesn't work as the table (Scope) is renamed each time on the new sheet.


    Code
    Private Sub AddRow_Click()
  ActiveSheet.ListObjects("Scope").ListRows.Add AlwaysInsert:=True
End Sub



    Any ideas how to get around this?


    Can this be done without creating a table (Just a range) and by inserting a new line at the bottom of the Range, but only copy and paste the formatting and formulas?
    or can this be accomplished by changing the VBA code to modify the Tables name (Scope) to match the new Table name created for the new sheet?


    I hope this is explained sufficiently.


    Thank you

    Re: Add rows to tables


    A way to do this is to define the new-sheet's name by counting them and pick the last's one. Of course, this means that your new sheet must always be the last of your sheets (or at the same place).


    When I need to do exactly the same thing in different sheets, I prefer to have the macro on my ribbon tab. So I just use the activesheet as my sheet.

    Re: Add rows to tables


    Hi
    I tried that but there is another problem which occurs from that method. The Sheet Tab is VBA named from a cell (Customer Name)
    Easy enough to define the last sheet but the name is always different.


    At the moment I have come up with some code to add new lines but need to firstly is to select the last cell on the left of the Range! Any ideas how to do this?
    Then I believe I have the solution.


    Will post when complete


    Thank you

    Re: Add rows to tables


    Refer to the table by its index number rather than its name. If there is just the one table on the new sheet then the index number will be 1.


    If you mean you need to select the cell that is last row and first column of the table then use:

    Code
    Private Sub AddRow_Click()
    With ActiveSheet.ListObjects(1)
        .ListRows.Add
        Application.Goto .ListColumns(1).Range.Rows(.ListRows.Count + 1)
    End With
End Sub


    Note that as True is the default value for AlwaysInsert there is no need to specify that in the code.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Add rows to tables


    Thanks for the reply, Much appreciated.
    I managed to get something sorted for inserting rows, but I need to modify the following, which appears to be working ok. However this code only selects the last cell in column (D) which has data in it.
    When I add a new row, there is no data in it and I would like the newly created row to be selected at the end of my code.


    Question is, How do I make the code below select the new blank cell which is one below?


    Code
    Sub SelectLast()
Range("C" & Rows.Count).End(xlUp).Select
End Sub

    Re: Add rows to tables


    Code
    Sub SelectLast()
    Cells(Rows.Count, 3).End(xlUp).Offset(1).Select
End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Add rows to tables


    You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login