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
Personal tools
Namespaces

Variants
Actions
Navigation
Categories
Toolbox