Search for empty cell then return a specific number of cells before it

[elsaidmi]

Hi, I'm trying to find an empty cell, then return the cell 1 before it, then the cell 2 before it, then the cell 3 before it, so that my summary page is always showing only the last 13 headers. Any idea on what this formula would be?

Thank you in advance!

 

[veryamusing]

Two methods using VBA:

Method 1

Sub findHeaders()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim firstEmpty As Range: Set firstEmpty = ws.Columns("B").Find("", [B1], xlValues, xlWhole, xlByRows, xlNext, True)
    Dim var1 As Variant: var1 = Application.Transpose(firstEmpty.Offset(-13, 0).Resize(13, 1).Value2)
End Sub

Or:
Method 2

Sub findHeaders()
    Dim var1 As Variant: var1 = Application.Transpose(Columns("B").Value2)
    Dim var2(1 To 13) As Variant
    Dim i As Long, j As Long
    For i = LBound(var1) To UBound(var1)
        If IsEmpty(var1(i)) Then
            i = i - 1
            Exit For
        End If
    Next
    For j = LBound(var2) To UBound(var2)
        var2(j) = var1(j + (i - 13))
    Next
End Sub

 

[Fluff]

Assuming that your headers as in row 1 try

Sub GetHeaders()

   Range("A1").End(xlToRight).Offset(, -13).Resize(1, 13).Copy Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range("A1")

End Sub

Change the sheet name in red to match your summary sheet. The sheet you're copying from needs to be active