How to Move Footers to the Top of Excel Tables

This article describes how to use an Excel Macro to move all of the footers from a set of exported tables to the tops of the tables. Excel macros use a programming language called Visual Basic to perform repetitive tasks automatically. The macro will move the footer information below the question name. The macro assumes that you have one table per worksheet, and that the table is placed in the default position with the question name in the cell A1.

Method

To use the macro you need to make sure that macros have been enabled and that the Developer tab is shown in the Excel ribbon.

The process for enabling the Developer tab can be found here.

The process for enabling Macros can be found here.

Once macros are enabled and you have exported a batch of tables, add the below macro to your Excel worksheet and run it. To do so:

1. Select the Developer tab.
2. Click on the Visual Basic button in the top left. This will open a new window called Microsoft Visual Basic for Applications.
3. In the window on the left-hand side, you will see a list of folders. Select the folder that contains the name of your workbook.
4. Right-click it and select Insert > Module.
5. Click on Module 1. This will open a box which will have the name of your workbook and Module 1 at the top.
6. Paste the code below into this window.
7. Go back to the main Excel window containing your tables.
8. Click the Macros button in the top left.
9. Click on the macro named FootersToTop.
10. Click Run.

Sub FootersToTop() 

    ' Loop over the sheets in the workbook
    For Each sht In ActiveWorkbook.Worksheets     

        ' Select the current sheet in the list
        sht.Select
  

        'find how long the footer is
        'Find the last row
        Dim LastRow As Long
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With    

        'find last row of table
        Dim LastRowTab As Long
        With ActiveSheet
            LastRowTab = .Cells(.Rows.Count, "B").End(xlUp).Row
        End With     

        'calc how many lines the footer is on
        Dim footerlines As Long
        footerlines = LastRow - LastRowTab     

        ' Insert a blank row(s) at the top
        Rows("2:" & (1 + footerlines)).Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

        'Cut and paste footer to top cell
        Range("A" & LastRowTab + footerlines + 1 & ":A" & LastRow + footerlines + 1).Select
        Selection.Cut
        Range("A2").Select
        ActiveSheet.Paste      

    Next sht
End Sub

Next

How to Export to Office