Cut and Paste Last Fourteen Rows

Hi all,

I have several similarly formatted daily reports with a detailed upper section and a 14-row summary lower section. Currently, I have to manually format them to print on letter size paper - which is very time consuming.

I need to cut and paste the summary section to a new sheet, and then repeat the Row 4 column header on all the pages for the detailed section. (I think I would be able to add the column widths and other formats.)

Can someone please help me with some codes.

Thank you very much,
Gos-C

This might give you a start.

Option Explicit

Sub CutAndPasteLast14Rows()
    Dim LastRow As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    ' assumes your headers are in row 1 and sheet2 exists and is empty
    
    Range("A1").EntireRow.Copy Sheets("Sheet2").Range("A1")
    
    Range("A" & LastRow - 13 & ":A" & LastRow).EntireRow.Cut Sheets("Sheet2").Range("A2")

End Sub

Hi Marcol,

Very good start! Thank you.

To clarify, the headers are in row 4 and are for the detailed section if it cannot fit on one pages. So, I need the code to add the headers on any subsequent page (currently, those pages do not show the headers).

Sheet2 does not exist - the reports come with just one sheet.

Also, is my substitution to find the last row a safer bet?

Option Explicit

Sub CutAndPasteLast14Rows()
    Dim LastRow As Long

    'LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ' assumes your headers are in row 1 and sheet2 exists and is empty
    
    Range("A1").EntireRow.Copy Sheets("Sheet2").Range("A1")
    
    Range("A" & LastRow - 13 & ":A" & LastRow).EntireRow.Cut Sheets("Sheet2").Range("A2")
End Sub

Hi all,

Together with Marcol's code and another one that I found

HTML Code: 

, here is my solution:

Option Explicit
Sub CutAndPasteLast14Rows()
    Dim LastRow As Long
    
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Worksheets.Add
ActiveSheet.Name = "Summary"
Range("A" & LastRow - 13 & ":A" & LastRow).EntireRow.Cut Sheets("Summary").Range("A1")
    ' assumes your headers are in row 1 and sheet2 exists and is empty
ActiveSheet.UsedRange.Select
With Selection.Font
    .Name = "Lucide Console"
    .Size = 7
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
End With
ActiveSheet.Columns("A:A").ColumnWidth = 22.71
Rows("1:14").RowHeight = 12
End Sub

Function Check_PageBreak()
    Const sHdr      As String = "DOC/DWG #"
    Dim iView       As XlWindowView
    Dim iHdrRow     As Long
    Dim oHPB        As HPageBreak
    Dim bFlag       As Boolean
    Dim iRow        As Long

    iView = ActiveWindow.View
    ActiveWindow.View = xlPageBreakPreview
    
    With Sheet1
        .ResetAllPageBreaks
        iHdrRow = Columns(4).Find(What:=sHdr, _
                                  LookIn:=xlValues, LookAt:=xlWhole, _
                                  MatchCase:=False).Row
        Do
            bFlag = False
            For Each oHPB In Sheet1.HPageBreaks
                With oHPB
                    If .Type = xlPageBreakAutomatic And _
                       .Location.Row > iHdrRow Then
                        bFlag = True
                        iRow = .Location.Row
                        Rows(iHdrRow).Copy
                        .Location.EntireRow.Insert
                        Sheet1.HPageBreaks.Add Before:=Rows(iRow)
                        Exit For
                    End If
                End With
            Next oHPB
        Loop While bFlag
    End With
    
    ActiveWindow.View = iView
End Function

It works perfectly!

Thank you, Marcol.

Regards,
Gos-C

I would use the code I gave you for LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Change the column letter, as required,to a known column that will always have data for the last required row.
This is more controllable than using the Find method, that finds the last used row in the sheet.

Let's say that we know that column "A" will always contain the last row of relevant data and there are no more irrelevant entries in that column, and for example that row is 20.

If there is unrequired data in say Range("C25"), let's say a footnote you don't need to copy,or indeed seems to be empty but actually has a string of spaces ,

LastRow = Range("A" & Rows.Count).End(xlUp).Row

will return 20

LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

will return 25

Valid point! I will do that. Thank you, Marcol.

Gos-C