How to Move Footers to the Top of Excel Tables

From Q
Jump to: navigation, search

This page 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 to. 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.

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: [1]

The process for enabling Macros can be found here: [2]

Once macros are enabled and you have exported a batch of tables, you should 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

Developertabvbbutton.jpg

Vbaddmodule.png

Sub FootersToTop()
    
    ' Loop over the sheets in the workbook

    For Each sht In ActiveWorkbook.Worksheets
        
        ' Select the current sheet in the list

        sht.Select
    
        ' Insert a blank row at the top

        Rows("2:2").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        
        'Find the last row

        Dim LastRow As Long
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
        
        'Cut and paste footer to top cell

        Range("A" & LastRow).Select
        Selection.Cut
        Range("A2").Select
        ActiveSheet.Paste
        
    Next sht

End Sub