New table if count is larger than 20
Results 1 to 5 of 5

Thread: New table if count is larger than 20
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default New table if count is larger than 20

    Hi

    I have a spreadsheet that uses code to insert rows into my table based on a cell value. My problem is the cell value can be over 100 and i would like to somehow break the table up if value is 20. So if value is 20 then start a new table. This is purely for printing purposes (header on each printed page, followed by 20 rows of data(with subtotal if possible)) so if anyone has a better idea instead of creating a new table then i would appreciate your idea. Thanks MC

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,062
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: New table if count is larger than 20

    There is a setting to repeat headers on every printed page.

    Print rows with column headers on top of every page
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: New table if count is larger than 20

    Hi have gathered the following code:

    Sub insertbreak()

    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("Table1")
    If tbl.DataBodyRange.Rows.Count <= 20 Then
    MsgBox "do nothing"
    Else
    MsgBox "insert break"
    End If
    End Sub

    i think i would manage to insert the page break but could you advise on how to insert subtotal after the 20 rows.
    Thanks MC
    Last edited by mc136355; Aug 25th, 2019 at 12:43 PM.

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,974
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: New table if count is larger than 20

    Try this :
    Assuming the table start at colomn A & header at row 1.
    First, you need to manually add the subtotal formula in row after the last row of the table.
    I'm using loop to show every 20 row and hide the rest.
    You can check by inserting a break point in line with k = i + 1 then hit F5 repeatedly, see what happen.
    But I'm not sure about how to do the print part, Im using this method (but for the moment I commented the lines):
    ' .Range.Select
    ' ActiveWindow.RangeSelection.PrintOut


    Code:
    Sub a1107921a()
    'https://www.mrexcel.com/forum/excel-questions/1107921-new-table-if-count-larger-than-20-a.html
    Dim i As Long, k As Long, rx As Long, rc As Long, j As Long
    With ActiveSheet.ListObjects("Table1")
        rx = .Range.Rows.count
        k = 2
        For i = 2 To rx - 1
            j = i - 1
            If j Mod 20 = 0 Or i = rx - 1 Then
            Range("A2:A" & rx - 1).EntireRow.Hidden = True
            Range("A" & k & ":B" & i).EntireRow.Hidden = False
            k = i + 1
                'DO THE PRINT JOB HERE
                '.Range.Select
                'ActiveWindow.RangeSelection.PrintOut
            End If
        Next
        
        .Range.EntireRow.Hidden = False
    '    MsgBox "It's done"
    End With
    End Sub

  5. #5
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,974
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: New table if count is larger than 20

    This one is more flexible than the code I gave you above.
    The table doesn't need to start at col A & the header doesn't need to be at row 1.

    Code:
    Sub a1107921b()
    'https://www.mrexcel.com/forum/excel-questions/1107921-new-table-if-count-larger-than-20-a.html
    Dim i As Long, k As Long, rx As Long, x As Long, j As Long
    With ActiveSheet.ListObjects("Table1").DataBodyRange
        
        rx = .Rows.count
        x = 20 'limit every how many rows
        k = 1
        For i = 1 To rx
            If i Mod x = 0 Or i = rx Then
                .EntireRow.Hidden = True
                .Cells(k, 1).Resize(x).EntireRow.Hidden = False
                k = i + 1
                    'DO THE PRINT JOB HERE
                    '.Range.Select
                    'ActiveWindow.RangeSelection.PrintOut
            End If
        Next
        
        .EntireRow.Hidden = False
    '    MsgBox "It's done"
    End With
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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